http://www.vbaclass.com/blog/excel-vba-tutorial-learn-to-use-vba-to-overcome-macro-recorder-limitations/
If you record a macro where you sort your data, the macro records the last contiguous row in the data set. Next time you run the macro, if you have more rows of data the recorded macro wont correctly sort to the new last row. Also, anytime you record a macro to sort data, the macro records the name of the sheet where you sorted the data. If your data appears on a sheet with a different name, the macro won’t work.
In our previous blog tutorial I instructed how to dim a variable to capture change (see http://www.vbaclass.com/blog/excel-vba-class-free-vba-tutorial-variables/).
In this lesson I will explain how to use Excel Visual Basic Applications to find the last row of data and concatenate a variable into a recorded macro.
To follow along, download this sample workbook.
Many VBA blogs recommend what I consider an ineffective method of locating the last row of data. Those blogs recommend you use the Goto > Special > Last Cell command.
Goto Command (Keyboard shortcut is Ctrl+G)
Goto Dialog – Special Button
Goto Special Dialog
In VBA, the code to achieve that command is
Selection.SpecialCells(xlCellTypeLastCell).Select
However theres a potential problem with using that command. If you have deleted rows or columns, this brings you to the end of the deleted data, not the active data. Once data is placed in a cell the cells status changes from clean to dirty. The Goto > Special > Last Cell command may bring you past the end of the actual data.
If you try Goto > Special > Last cell on the Sheet1 worksheet tab in the workbook you downloaded (link) notice you end up in an empty cell. There used to be data there, but it was deleted. The Goto > Special > Last Cell command isn’t aware of this condition.
A more reliable method is, assuming you know that you have data starting in column A, go to the bottom of the spreadsheet, like cell A1000000. From cell A1000000 press ctrl+up arrow key to jump to the last row of data.
In VBA I would code this as follows:
Range(“A1000000”).Select
Activecell.end(xlup).select
Dim lastrow
Lastrow=activecell.row
In this code, the lastrow variable stores the row of the activecell, the last cell in column A that contains a value.
Now that youve located the last row you can concatenate your lastrow variable into any Excel macro.
In Excel, the concatenation operator is the & symbol. You could use the & concatenation operator in a formula as follows:
In VBA the concatenation operator works in a similar fashion with variables.
In the workbook you’ve downloaded previously (link) Sheet1 has 86 rows of data, while Sheet2 has 89 rows of data. I recorded a macro to sort the data on Sheet1 by State (column C). Here is the code generated by sorting the data.
Sub Macro1()
Range(“C3”).Select ‘I right clicked on cell C3 to access the sort command
‘*******************All of this code is a sort command in VBA************* ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear ‘Problem with the sheet name
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“Sheet1”).Sort
.SetRange Range(“A1:H86”) ‘Here is where the macro recorder hard-codes row 86
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
‘******************End of the Data Sort command***************************
End Sub
Now, if you try to run the macro on Sheet2, it fails for two reasons. First, the recorded macro hard-codes the name of the sheet three times. Even if we fix the sheet name issue, it will still only sort to row 86, but Sheet2 has 89 rows of data.
To fix this problem we need to substitute the activesheet object for the code that reads Worksheets(“Sheet1”). In the updated code shown below, i have commented out the bad lines and marked where i used the activesheet object. The second issue is the last row as Range(“A1:H86”), that’ where i’ll concatenate the lastrow variable. The edited macro appears below
Sub Macro2()
‘*************Add Code To Find The Last Row*************
Range(“A1000000”).Select
ActiveCell.End(xlUp).Select
Dim Lastrow
Lastrow = ActiveCell.Row
‘*************End of Code To Find The Last Row*************
Range(“C3”).Select
‘*************Commented Out Recorded Macro Code*************
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Clear
‘ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
‘With ActiveWorkbook.Worksheets(“Sheet1”).Sort
‘*************Code using Activesheet Instead*************
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(“C3”), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
‘*************Concatenate Last Row Here*************
‘.SetRange Range(“A1:H86”) ‘I commented out the recorded macro code here
.SetRange Range(“A1:H” & Lastrow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
I hope you will find this free Microsoft Excel VBA Tutorial useful. If you have questions or comments please contact me for assistance. If you have other topics you recommend I should blog about with Excel VBA please feel free to make suggestions.
To contact Excel Class Training about Excel VBA Classes visit our contact page here