CODEWITHSUNDEEP
excelvba
John Wilson
John Wilson

Reputation: 112

Data from next visible row on different sheet

This is probably in here somewhere but I'm having trouble finding it. Looking to get a value from a filtered range on a different sheet without having to set up a loop or going to that other sheet. For unfiltered data this works: I'm working from a sheet named "Main"

for x = 1 to whatever
SelRow = Sheets("Data").Range("D:D").Find(Range("D" & x)).row
MsgBox Sheets("Data").Range("A" & SelRow + 1)
next x

The above will get me the value of whatever is in Col "A" on SelRow + 1 if the Data sheet is unfiltered but if I have a filter in place what I need is whatever is in Col "A" on the 1st visible row after the SelRow on the Data sheet.

Thank you

Upvotes: 0

Views: 38

Answers (1)

user10970498
user10970498

Reputation:

You need to find the first visible cell after the found cell. This can be easily accomplished with the Range.SpecialCells method.

Option Explicit

Sub Macro1()

    Dim x As Long, whatever As Long, m As Variant, wsm As Worksheet

    Set wsm = Worksheets("main")

    With Worksheets("data")

        whatever = wsm.Cells(.Rows.Count, "D").End(xlUp).Row

        For x = 1 To whatever
            m = Application.Match(wsm.Cells(x, "D"), .Range("D:D"), 0)
            If Not IsError(m) Then
                Debug.Print .Range(.Cells(m + 1, "A"), .Cells(.Rows.Count, "A")).SpecialCells(xlCellTypeVisible).Cells(1).Value
            End If
        Next x

    End With

End Sub

Upvotes: 1

Related Questions