Get row number in excel(Involves hidden row)

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?

1 Like

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

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
    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

Hi Jack,

Thanks for the idea, it works like charm!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.