1

please how to determine in vba if first visible (after using filter) cell equals to the last visible cell?

If I have:

A5 = a, B5 = 1

A6 = b, B6 = 2

A7 = c, B7 = 1

A8 = a, B8 = 1

A9 = b, B9 = 2

Then If I filter out "1", column A will be: a, c, a. Then I need to do nothing.

But, when I filter out "2", Column A will be: b, b. Then I need to be C5 for example = b.

This

Range("A200").End(xlUp).SpecialCells(xlCellTypeVisible).Value

I cant use, because it will use also A1, A2, A3 and A4 (I suppose), but I need to be A5 the "last one".

I have this:

If Range("A5").End(xlDown).Value <> "first visible cell in Range("A5:A200")" Then
    ThisWorkbook.Sheets("Šablona").Range("B2").Value = ""
Else
    ThisWorkbook.Sheets("Šablona").Range("B2").Value = "first visible cell"
End If
  • 1
    Are you using VBA for other things, or would a formula-based solution work for you? – Werrf Jun 9 '17 at 11:39
  • Formula based would work. – Roman Žydyk Jun 9 '17 at 11:52
  • I have the edge of an idea - I thought I had it, but then noticed a problem. I'm working on it. – Werrf Jun 9 '17 at 12:35
1

With data like:

enter image description here

We apply a filter and want to know if the first visible cell in column A has the same value as the last visible cell in column A.

This short macro loops over the range, tests for visibility, and performs the test:

Sub Roman()
    Dim rng As Range, r As Range
    Dim v1 As Variant, v2 As Variant
    Dim FlipFlop As Boolean

    Set rng = Range("A2:A26")
    FlipFlop = True

    For Each r In rng
        If FlipFlop Then
            If r.EntireRow.Hidden = False Then
                v1 = r.Value
                FlipFlop = False
            End If
        Else
            If r.EntireRow.Hidden = False Then
                v2 = r.Value
            End If
        End If
    Next r

    If v1 = v2 Then
        MsgBox "they are equal"
    Else
        MsgBox "they are not equal"
    End If

End Sub

enter image description here

This does not depend on SpecialCells.

1

ok, figured out:

=INDEX(A3:A200;MIN(IF(SUBTOTAL(3;OFFSET(A3;ROW(A3:A200)-ROW(A3);0));ROW(A3:A200)-ROW(A3)+1)))

returns first value, and:

=LOOKUP(2;1/((SUBTOTAL(3;OFFSET(A3:A65535;ROW(A3:A65535)-MIN(ROW(A3:A65535));0;1)))*(1-ISBLANK(A3:A65535)));A3:A65535)

returns last value.

Thanks for help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.