Get row number in excel(Involves hidden row)

[ATO]

Hi All,

I am looking for a way on how to get the first row number in a datatable.
In my case, my first data row(dynamic range) is/are hidden, I need to put a remark next to the unprocessed data row(Column B).

Row 1 is the header, row 2 is hidden, row 3 is the first data row.

Had tried with ‘Find first/last row’ activity, it managed to capture hidden row 2 as the first data row which is not right to my case.

What can I do to capture this dynamic first visible row number and use it in write cell to column B?

[muhamed_fasil]

@ATO

den row 2 as the first data row which is not right to my case.

What can I do to capture this dynamic first row number and use it in write cell to column B?

When you use read range i dont think the hidden rows value will be getting into it

[jack.chan]

use vba to get the last visible row in column B @ATO

1. put below code in text file e.g. vba.txt
2. in excel application scope use invoke vba method as shown below
   in entryMethodParametrs pass your sheet name
   outputValue = lastRow (Object variable)
   

you can use the above activity repeatedly to keep getting the last visible row

Function GetLastVisibleRow(sheetName As String) As Integer
    Dim firstCell As Range
    Sheets(sheetName).Activate
    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row
    
    If lastRow = 1 Then
        'lastRow = Rows.Count
    End If
    
    Set firstCell = ActiveSheet.Range("B" & (lastRow + 1), "B" & Rows.Count).SpecialCells(xlCellTypeVisible)(1)
   
    GetLastVisibleRow = firstCell.Row
 
End Function

[ATO]

Hi Jack,

Thanks for the idea, it works like charm!