{"id":3162,"date":"2021-03-27T10:05:41","date_gmt":"2021-03-27T17:05:41","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3162"},"modified":"2021-03-27T10:13:39","modified_gmt":"2021-03-27T17:13:39","slug":"three-best-ways-to-find-last-rownon-blank-and-column-using-vba","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3162","title":{"rendered":"Three Best Ways to Find Last Row(Non-blank) and Column Using VBA"},"content":{"rendered":"\n
Finding last used row and column is one of the basic and important task for any automation in excel using VBA. For compiling sheets, workbooks and arranging data automatically, you are required to find the limit of the data on sheets.<\/p>\n\n\n\n
This article will explain every method of finding last row and column in excel in easiest ways. 1. Find Last Non-Blank Row in a Column using Range.End<\/strong> Let\u2019s see the code first. I\u2019ll explain it letter.<\/p>\n\n\n\n
Sub getLastUsedRow()\n\n Dim last_row As Integer\n\n last_row = Cells(Rows.Count, 1).End(xlUp).Row \u2018This line gets the last row\n\n Debug.Print last_row \n\nEnd Sub<\/pre>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/p>\n\n\n\n
The the above sub finds the last row in column 1.
How it works?<\/strong> It is just like going to last row in sheet and then pressing CTRL+UP shortcut. Cells(Rows.Count, 1):<\/strong>\u00a0This part selects cell in column A. Rows.Count gives 1048576, which is usually the last row in excel sheet.<\/p>\n\n\n\n
Cells(1048576, 1)<\/pre>\n\n\n\n
.End(xlUp):<\/strong> End is an method of range class which is used to navigate in sheets to ends. xlUp is the variable that tells the direction. Together this command selects the last row with data.<\/p>\n\n\n\n
Cells(Rows.Count, 1).End(xlUp)<\/pre>\n\n\n\n
.Row : <\/strong>row returns the row number of selected cell. Hence we get the row number of last cell with data in column A. in out example it is 8.<\/p>\n\n\n\n
See how easy it is to find last rows with data. This method will select last row in data irrespective of blank cells before it. You can see that in image that only cell A8 has data. All preceding cells are blank except A4.<\/p>\n\n\n\n
Select the last cell with data in a column<\/strong> If you want to select the last cell in A column then just remove \u201c.row\u201d from end and write .select.<\/p>\n\n\n\n
Sub getLastUsedRow()\n\nCells(Rows.Count, 1).End(xlUp).Select \u2018This line selects the last cell in a column\n\nEnd Sub<\/pre>\n\n\n\n
The \u201c.Select\u201d command selects the active cell. Get last cell\u2019s address column<\/strong> If you want to get last cell\u2019s address in A column then just remove \u201c.row\u201d from end and write .address.<\/p>\n\n\n\n
Sub getLastUsedRow()\n\nadd=Cells(Rows.Count, 1).End(xlUp).address \u2018This line selects the last cell in a column\nDebug.print add\nEnd Sub<\/pre>\n\n\n\n
The Range.Address<\/strong> function returns the activecell\u2019s address. Find Last Non-Blank Column in a Row<\/strong> It is almost same as finding last non blank cell in a column. Here we are getting column number of last cell with data in row 4.<\/p>\n\n\n\n
Sub getLastUsedCol()\n\n Dim last_col As Integer\n\n last_col = Cells(4,Columns.Count).End(xlToLeft).Column \u2018This line gets the last column\n\n Debug.Print last_col\n\nEnd Sub<\/pre>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
You can see in image that it is returning last non blank cell\u2019s column number in row 4. Which is 4. How it works?<\/strong><\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/p>\n\n\n\n
Well, the mechanics is same as finding last cell with data in a column. We just have used keywords related to columns. Select Data Set in Excel Using VBA<\/strong> Now we know, how to get last row and last column of excel using VBA. Using that we can select a table or dataset easily. After selecting data set or table, we can do several operations on them, like copy-paste, formating, deleting etc. Here we have data set. This data can expand downwards. Only the starting cell is fixed, which is B4. The last row and column is not fixed. We need to select the whole table dynamically using vba.
VBA code to select table with blank cells<\/p>\n\n\n\n
Sub select_table()\nDim last_row, last_col As Long\n\n'Get last row\nlast_row = Cells(Rows.Count, 2).End(xlUp).Row\n\n'Get last column\nlast_col = Cells(4, Columns.Count).End(xlToLeft).Column\n\n'Select entire table\nRange(Cells(4, 2), Cells(last_row, last_col)).Select\n\nEnd Sub<\/pre>\n\n\n\n
<\/figure><\/div>\n\n\n\n
When you run this, entire table will be selected in fraction of a second. You can add new rows and columns. It will always select the entire data.
Benefits of this method:<\/strong><\/p>\n\n\n\n
It\u2019s easy. We literally wrote only one line to get last row with data. This makes it easy.<\/li>
Fast. Less line of code, less time taken.<\/li>
Easy to understand.<\/li>
Works perfectly if you have clumsy data table with fixed starting point. <\/li><\/ol>\n\n\n\n
Cons of Range.End method:<\/strong><\/p>\n\n\n\n
The starting point must be know.<\/li>
You can only get last non-blank cell in a known row or column. When your starting point is not fixed, it will be useless. Which is very less likely to happen.<\/li><\/ol>\n\n\n\n
2. Find Last Row Using Find() Function<\/strong> Let\u2019s see the code first.<\/p>\n\n\n\n
As you can see that in image, that this code returns the last row accurately. How it works?<\/strong> Here we use find function to find any cell that contains any thing using wild card operator “*”. Asterisk is used to find anything, text or number.<\/p>\n\n\n\n
We set search order by rows (searchorder:=xlByRows). We also tell excel vba the direction of search as xlPrevious (searchdirection:=xlPrevious). It makes find function to search from end of the sheet, row wise.\n\nOnce it find a cell that contains anything, it stops. We use the Range.Row method to fetch last row from active cell.<\/pre>\n\n\n\n
Benefits of Find function for getting last cell with data:<\/strong><\/p>\n\n\n\n
You don\u2019t need to know the starting point. It just gets you last row. <\/li>
It can be generic and can be used to find last cell with data in any sheet without any changes.<\/li>
Can be used to find any last instance of specific text or number on sheet.<\/li><\/ol>\n\n\n\n
Cons of Find() function:<\/strong><\/p>\n\n\n\n
It is ugly. Too many arguments.<\/li>
It is slow.<\/li>
Can\u2019t use to get last non blank column. Technically, you can. But it gets too slow.<\/li><\/ol>\n\n\n\n
3. Using SpecialCells Function To Get Last Row<\/strong> The SpecialCells function with xlCellTypeLastCell argument returns the last used cell. Lets see the code first<\/p>\n\n\n\n
Sub spl_last_row_()\nlastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)<\/strong>.Row\nDebug.Print lastRow\nEnd Sub<\/pre>\n\n\n\n
<\/figure><\/div>\n\n\n\n
If you run the above code, you will get row number of last used cell.<\/p>\n\n\n\n
How it Works?<\/strong><\/p>\n\n\n\n
This is the vba equivalent of shortcut CTRL+End in excel. It selects the last used cell. If record the macro while pressing CTRL+End, you will get this code.<\/p>\n\n\n\n
Sub Macro1()\n'\n' Macro1 Macro\n'\n\n'\n ActiveCell.SpecialCells(xlLastCell).Select\nEnd Sub\n<\/pre>\n\n\n\n
We just used it to get last used cell\u2019s row number.<\/p>\n\n\n\n
Note:<\/strong> As I said above, this method will return the last used cell not last cell with data. If you delete the data in last cell, above vba code will still return the same cells reference, since it was the \u201clast used cell\u201d. You need to save the document first to get last cell with data using this method.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/figure><\/div>\n\n\n\n
<\/figure><\/div>\n\n\n\n