Last cell value of an excel coulmn where we have different lengths of coulmns

[RPAJob_Opportun]

Hi Team, I am trying to retrieve the last cell of a column and in my excel sheet I have different lengths of column… so I am not able to use the get row number action from Microsoft Excel VBA, as it will count the row numbers where each column have same number of rows.

I want to get the count of the rows for a specific column as I have columns with different row counts. Any help will be much AppreciatedPost

------------------------------
Mithun Nahak
------------------------------

[NileshJadhavBP · Accepted solution]

Hi Mithun,

Please use below code to get your last row.

Dim LR As Long

LR = Cells(Rows.Count, 1).End(xlUp).Row (Put your column number where 1 is mention)

I hope it will help you.

Thanks
Nilesh

------------------------------
Nilesh Jadhav
Senior RPA Specialist
ADP
India
------------------------------

[AntonHosang1]

You should guard against the last cell in the worksheet having data.

Dim lastCell As Object
'Please arrange sheet names and column indexes for user input
lastCell = Sheet1.Cells(Sheet1.Rows.Count, 1)

Dim lastRow As Integer
lastRow = IIf(lastCell.Value <> "", lastCell.Row, lastCell.End(-4162).Row)

------------------------------
Anton Hosang
RPA Developer
Smart Automation Services
Europe/London
------------------------------

[Kurt_EfraimPara]

Hello Nilesh, i get error in code stage using xlUp, is not declared, inaccessible due to protection level. how do i declare xlUp

------------------------------
Kurt Efraim Paraiso
------------------------------

[ManpreetKaur1]

Hi @Kurt Efraim Paraiso,

Instead of using xlUp you can use -4162 as value and it should resolve your issue. xlUp is just an enum whose value is -4162​

------------------------------
Manpreet Kaur
Manager
Deloitte
------------------------------