CODEWITHSUNDEEP
vbaexcel
TazgerO
TazgerO

Reputation: 535

Retrieving a list of row number from only visible cells

Let's assume, I have the following table:

Toto B June 12pm
Titi A April 11pm
Toto D January 9am
Toto F December 8am

I want to extract at each line some data and fill a logsheet. I have done the macro that work for the whole data. However I need to extract only a selection of data now on, let's say only the data having their first column equal to "Toto".

To this end, I am using the Autofilter manually (user friendliness) and then extract only the visible cell meeting the criteria using

LastLine = Range("A" & Rows.Count).End(xlUp).Row
Export_Range = Workbook.Sheets(1).Range("A2:A" & LastLine).Rows.SpecialCells(xlCellTypeVisible)

To avoid rewriting the macro, I'd like to know if there is a way of outputting a list of row number meeting the criteria. I have search a bit and I haven't been lucky. Any idea?

Upvotes: 2

Views: 1175

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

This will list them:

Sub ListThem()
    Lastline = Range("A" & Rows.Count).End(xlUp).Row
    msg = ""

    For Each r In Range("A2:A" & Lastline).Cells.SpecialCells(xlCellTypeVisible)
        msg = msg & r.Row & vbCrLf
    Next r
    MsgBox msg
End Sub

This assumes that AutoFilter has already been applied.

Upvotes: 2

Related Questions