Find Row Number for 25th Visible Row

[Caleeco]

Hello,

I am trying to figure out a bit of code which will fit into a much larger module.

Basically I have an Excel Table, for which the Header Row is in row 6. The table may or may not be filtered.

I want to be able to find the row number of the 25th visible row (even if it is blank and outside of my table range). Step one in my troubleshooting is to just select the range. The code below works, but it also selects hidden rows as part of the '25'.

I don't wish to step through each row in a loop checking the Hidden property of each cell and doing a count if possible.

Any ideas where I'm going wrong?

Sub Find_25th_Row()
    Dim myUniqueCells As Range
    Set myUniqueCells = Sheets(“Sheet1").AutoFilter.<wbr>Range.Offset(1, 0).Resize(25, 1).Cells.SpecialCells(<wbr>xlCellTypeVisible)
    myUniqueCells.Select
End Sub

Help/Guidance is much appreciated
Thanks
Caleeco

 

[JoeMo]

If "Excel Table" means a list object, maybe one of those list object quirks. Try this after you replace the bold red font with the name of your table.

 Set myUniqueCells = Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.Resize(25, 1).SpecialCells(xlCellTypeVisible)
 myUniqueCells.Select

 

[mikerickson]

This does loop through Areas, but its better than looping through cells.

Function NthVisibleRow(N As Long)
    Dim VisCells As Range, oneArea As Range
    Dim N As Long
    
    With ActiveSheet
        Set VisCells = .Cells.SpecialCells(xlCellTypeVisible)
    End With
    For Each oneArea In VisCells.Areas
        If oneArea.Rows.Count >= N Then
            NthVisibleRow = oneArea.Cells(N, 1).Row
            Exit Function
        Else
            N = N - oneArea.Rows.Count
        End If
    Next oneArea
End Function

 

[Caleeco]

If "Excel Table" means a list object, maybe one of those list object quirks. Try this after you replace the bold red font with the name of your table.

 Set myUniqueCells = Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.Resize(25, 1).SpecialCells(xlCellTypeVisible)
 myUniqueCells.Select

Hi Joe,

Thanks for the reply. Yes, I did mean list object! Sorry for not being clearer.

I added the reference to the LO as suggested, the result is the same unfortunately. Im not sure why the SpecialCells(xlCellTypeVisible) property won't exclude filtered rows within the table. Very confusing!

Is there anything else I could try? Been pulling my hair out for a couple hours over it

Kind Regards
Caleeco

 

[Caleeco]

This does loop through Areas, but its better than looping through cells.

Function NthVisibleRow(N As Long)
    Dim VisCells As Range, oneArea As Range
    Dim N As Long
    
    With ActiveSheet
        Set VisCells = .Cells.SpecialCells(xlCellTypeVisible)
    End With
    For Each oneArea In VisCells.Areas
        If oneArea.Rows.Count >= N Then
            NthVisibleRow = oneArea.Cells(N, 1).Row
            Exit Function
        Else
            N = N - oneArea.Rows.Count
        End If
    Next oneArea
End Function

Thanks for the reply Mike. The code above seems to count the blank cells above the table (Headers are in row 6). Please see image below. I know I could counter this by just adding 6 to the final total, but that doesn't seem to work in all scenarios.

Thanks for your help thus far
Kind Regards

Caleeco

 

[mikerickson]

That returns the row number of the nth visible row on the sheet.
If you want the row number of the nth visible row in some range, change this part to match your needs.

With ActiveSheet
    Set VisCells = .Cells.SpecialCells(xlCellTypeVisible)
End With

so that VisCells starts on the first row that you consider significant.

Alternatly, if you want it to find the 25th visible row, ignoring the always visible first 6 rows, then change N to 31

 

[JoeMo]

Don't know if this helps, but it locates the 25th visible row below the table header row. Assumes hidden rows, if any, are all within the Table.

Sub VisibleRow25()
Dim FirstTableRow As Long, LastTableRow As Long, VisTableRows As Long, LastVisTableRow As Long, VisRow25 As Long
Dim myUniqueCells As Range
'The first (header) and last rows of the table are fixed regardless of the filter state of the Table
FirstTableRow = Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.Cells(1, 1).Row
LastTableRow = FirstTableRow + Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.Rows.Count - 1
Set myUniqueCells = Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For i = 1 To myUniqueCells.Areas.Count
    VisTableRows = VisTableRows + myUniqueCells.Areas(i).Rows.Count
Next i
'Next variable not needed, but locates the last Table row that's visible
LastVisTableRow = Split(Replace(myUniqueCells.Areas(myUniqueCells.Areas.Count).Address, ":", ""), "$")(4)
If VisTableRows >= 25 Then
    VisRow25 = FirstTableRow + 25
Else
    VisRow25 = LastTableRow + 1 + 25 - VisTableRows
End If
MsgBox VisRow25
End Sub

 

[Caleeco]

That returns the row number of the nth visible row on the sheet.
If you want the row number of the nth visible row in some range, change this part to match your needs.

With ActiveSheet
    Set VisCells = .Cells.SpecialCells(xlCellTypeVisible)
End With

so that VisCells starts on the first row that you consider significant.

Alternatly, if you want it to find the 25th visible row, ignoring the always visible first 6 rows, then change N to 31

Don't know if this helps, but it locates the 25th visible row below the table header row. Assumes hidden rows, if any, are all within the Table.

Sub VisibleRow25()
Dim FirstTableRow As Long, LastTableRow As Long, VisTableRows As Long, LastVisTableRow As Long, VisRow25 As Long
Dim myUniqueCells As Range
'The first (header) and last rows of the table are fixed regardless of the filter state of the Table
FirstTableRow = Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.Cells(1, 1).Row
LastTableRow = FirstTableRow + Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.Rows.Count - 1
Set myUniqueCells = Sheets("Sheet1").ListObjects("Table1").AutoFilter.Range.SpecialCells(xlCellTypeVisible)
For i = 1 To myUniqueCells.Areas.Count
    VisTableRows = VisTableRows + myUniqueCells.Areas(i).Rows.Count
Next i
'Next variable not needed, but locates the last Table row that's visible
LastVisTableRow = Split(Replace(myUniqueCells.Areas(myUniqueCells.Areas.Count).Address, ":", ""), "$")(4)
If VisTableRows >= 25 Then
    VisRow25 = FirstTableRow + 25
Else
    VisRow25 = LastTableRow + 1 + 25 - VisTableRows
End If
MsgBox VisRow25
End Sub

Thanks for the replies gents. I got it working using a combination of your suggestions, with a slight modification. I did it at work so don't have the code to hand, but the pseudo-code was something like this:

1. Using the Listobjects("Table1").DataBodyRange, COUNT how many of it's rows were visible (VisCount)
2. COUNT how many ListRows there were in the list object (regardless if they were hidden or not)

IF VisCount  >=25 THEN
       'use the code @[B][URL="https://www.mrexcel.com/forum/members/mikerickson.html"]mikerickson[/URL][/B] wrote (area loop)
ELSE 
       Nth row = 25 - VisCount + Listrows.count + 6
End IF

That seems to work pretty well, and expands beyond the list object range!

So thank you both for the input, may not have been able to do it without your help!

EDIT: @JoeMoe Just read your method, looks like we had a similar solution!

Kind Regards
Caleeco