topic RE: Last cell value of an excel coulmn where we have different lengths of coulmns in Product Forum

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

RPAJob_Opportun — Tue, 24 Nov 2020 04:28:00 GMT

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

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

NileshJadhavBP — Tue, 24 Nov 2020 12:08:00 GMT

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

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

AntonHosang1 — Tue, 24 Nov 2020 16:16:00 GMT

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

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

chambhaa — Tue, 20 Sep 2022 05:16:00 GMT

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

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

ManpreetKaur1 — Tue, 20 Sep 2022 10:00:00 GMT

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