Sometimes it is very necessary you get the last Column or last Row of your dataset in Excel

This is a simple VBA code to get the Last Column and Last Row in your dataset

Sub lastRow1()
lastRow2 = Cells(Rows.Count, 2).End(xlUp).Row

End Sub

What the above code does is that,

1. we are using the Cells object which takes ROW and COLUMN as parameters. So in the Rows parameter, we call Rows.Count which counts ALL rows in our entire Excel sheet. And in the Column parameter, we choose the column which has all data up to the very last row in our dataset.
2. Next we call the End function and pass the parameter xlUp which is like hitting Ctrl + Up arrow key from the bottom of your Excel Sheet up . (which will thus locate the very first point in your dataset which contains data as it moves all the way up from the bottom)
3. Lastly, we call the Row object to get the Row which is the Row Number

Now we will check how to get the last Column in your dataset

Sub lastCol1()
lstCol = Cells(1, Columns.Count).End(xlToLeft).Column
End Sub

1. we are using the Cells object which takes ROW and COLUMN as parameters. So in the Rows parameter, we choose the First or any row which has data fully filled up to the entire breath of our dataset. (this will mostly be your header row). Then in the Column parameter we will call  Columns.Count which counts ALL columns in our entire Excel sheet.
2. Next we call the End function and pass the parameter xlToLeft which is like hitting Ctrl + Left arrow key from the far righthand side  of your Excel Sheet . (which will thus locate the very first point in your dataset which contains data as it moves all the way from right to left)
3. Lastly, we call the Column object to get the Column which is the Column Number

Hope this helps.

(thumbnail image credit, excelcampus.dot.com)

People Who Read The Above Post Also Read This:

The Basics – Visual Basics Macro Programming Start Up Splitting Values in one Column to Multiple Columns using CASE Expression Statement SQL Server Automatically Open Excel and Send Report Using Batch and VBA Assigning a Simple Button to a VBA Code – Excel Microsoft Visual Basic