Excel report automation involves writing VBA that can adapt with your data. Data ranges rarely stay the same over time and a useful bit of VBA code to learn is how you can use Excel to find the last row in a data range and then call it within the rest of your script which will save you having to alter your Excel
VBA every time your Data Range changes. Read on to find out how.
To find the last row in a data range using Excel VBA you can use a variety of methods. Two useful methods to adopt are explained below along with some example code that you can copy and paste directly into your Excel VBA module:
Method 1 – Create a Variable
This method is quick and efficient and works well when you want to find the last row on a single worksheet within Excel VBA.
The first step is to define the last row, Excel needs to find where it is and then set that row number to a name so you can use it in future code without having to change the VBA yourself every time the range changes.
Below the a variable called LastRow is created and will always equal the last row of the active worksheet:
1 2 3 4 5 6
Sub LastRow() 'Find the last row of active worksheet in column 1 (aka col A) LastRow = Cells(Rows.Count, 1).End(xlUp).Row End Sub
Breaking the code down into plain English LastRow is now equal to the last row that contains data in column 1 of the active worksheet.
The last row is found by searching in column 1 from the bottom of the worksheet upwards, that way if you have any gaps in the data it will still find the true last row.
To base your last row count on a different column, for example column 3 (aka column C), then you just change the numeric value in the code from 1 to 3 like below:
1 2 3 4 5 6
Sub LastRow() 'Find the last row of active worksheet in column 3 (aka col C) LastRow = Cells(Rows.Count, 3).End(xlUp).Row End Sub
Calling the Lastrow variable in VBA
After setting the variable LastRow it can be used in place of the last row number in all places of your VBA code.
For example if you want to select the entire data range for columns A to C and you would code the following:
1 2 3 4 5 6
Sub LastRow() 'Select A1 to the last row of column C Range(“A1:C” &LastRow).Select End Sub
Method 2 – Create a Function
This method involves creating a custom function that can be called in the rest of VBA code. It is useful when you have to work on multiple data-sets that span across several worksheets and the reason for this is after setting up the function you do not have to keep resetting the lastrow variable, it can be called from just a single statement.
The first step is to insert a new module in your VBA Editor and add the following code to create the LastRow function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
'Create a function called LastRow searching in column A Function LastRow(sh as Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:=” * ”, _ After:=sh.Range(“A1”), _ LookAt:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row On Error Goto 0 End Function End Sub
Save the module and then you have a custom function called LastRow that can be used anywhere in your code in place of the last row number.
For example if you want to select the entire data range on the worksheet called Data for columns A to C and you would use the following::
1 2 3 4 5 6
Sub LastRow() 'Select A1 to the last row of worksheet "Data", column C Range(“A1:C” &LastRow(Worksheets(“Data”))).Select End Sub
Note that in this case you have to specify which worksheet the last row is calculated on but because it is called directly in the same line of code. This does provide a benefit when you have to repeat the process of finding the last row of data ranges multiple times across different worksheets.