[VBA] Select a row after a filter
Solved
Schtroumpf_Wiki
-
Schtroumpf_Wiki -
Schtroumpf_Wiki -
Hello,
I am currently working on an Excel application that processes data in a table with several thousand rows. I often need to filter to extract specific rows.
The problem is that after applying a filter, the code "Cells(3,1).Value" will give me the value of cell A3 instead of the first visible cell in the third row on the screen after the filter.
I was previously managing this with a lot of workarounds, but my application is running quite slow. So I thought there should be a code to directly get the value I need.
Thank you for your help!
I am currently working on an Excel application that processes data in a table with several thousand rows. I often need to filter to extract specific rows.
The problem is that after applying a filter, the code "Cells(3,1).Value" will give me the value of cell A3 instead of the first visible cell in the third row on the screen after the filter.
I was previously managing this with a lot of workarounds, but my application is running quite slow. So I thought there should be a code to directly get the value I need.
Thank you for your help!
2 réponses
To keep it simple, it's a database of film reels. I filter the database with a specific type of film, then I sort the different reels of that type by length. I then want to select the two rows containing the information about the reels with the two smallest lengths.
That is to say, select information from the first two rows visible on the screen. The reference of the reel being in the first column, if I type Cells(2,1).Value it gives me the reference of the first reel in the database but not the one that is visible. What I'm looking for, I don't know if it exists, is a code like:
CellsVisible(2, 1).Value (well, that one doesn't work ^^)
There you go, I hope I've been clear enough
Thank you!
That is to say, select information from the first two rows visible on the screen. The reference of the reel being in the first column, if I type Cells(2,1).Value it gives me the reference of the first reel in the database but not the one that is visible. What I'm looking for, I don't know if it exists, is a code like:
CellsVisible(2, 1).Value (well, that one doesn't work ^^)
There you go, I hope I've been clear enough
Thank you!
Have you tried my code above?
Then for your cell, just call it like this:
Cells(Lig, 1)
What do you say...
Sub test() Dim DrLig As Long, Lig As Long DrLig = Sheets("Feuil1").Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row For Lig = 2 To DrLig If Rows(Lig).Hidden = False Then MsgBox "first visible row = " & Lig Exit For End If Next End Sub Then for your cell, just call it like this:
Cells(Lig, 1)
What do you say...
Sub test() Dim DrLig As Long, Lig As Long DrLig = Sheets("Feuil1").Columns(1).Find("*", , , , xlByColumns, xlPrevious).Row For Lig = 2 To DrLig If Rows(Lig).Hidden = False Then MsgBox "first visible line = " & Lig Exit For End If Next End Sub