Determine if first visible cell equal to last

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
• 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

With data like: 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 This does not depend on SpecialCells.

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.