Excel VBA Tutorial: Learn To Use VBA To Overcome Macro Recorder Limitations

Reposted from
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)

Excel Goto Command

Goto Dialog – Special Button

Excel Goto Special

Goto Special Dialog

Goto Special Last Cell

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:

Concatenate Operator

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

Excel Class VBA Macro Adds IfError Functions To Every Formula

Excel Class VBA Macro Adds IfError Functions To Every Formula

Excel VBA MacroToday a student asked Excel Class if there was a macro to automatically add an IfError function to every formula on every sheet in a selected workbook, assuming there was not already an IfError function used.

On short notice the Excel Class instructor provided the following code which we wanted to share with our readers.

Remember that macros have no Undo command. Once you run this VBA macro the only undo is to close the file without saving changes. The macro leaves a selected workbook open so you can review the changes before deciding to save or not.

To use this code, press the Alt+F11 key to open the VB Editor.
Select the Insert menu > Module command.
Then paste the following code into the empty window:
To run the program, press the F5 key, or from Excel select the Ribbon View Tab > Macros button and run the AddIfError macro.

Public Sub AddIffError()
‘purpose: IF there is a formula add iferror
‘if there’s an iferror already, ignore the formula
‘process all sheets in wb

‘Handle Errors
On Error GoTo Errorhandler

‘Step 10: Ask user to open a wb
MsgBox “Please click OK to select a workbook to process”

‘Step20: Prompt user to open wb to process
With Application.FileDialog(msoFileDialogOpen)
.Show
.Execute
End With

‘Step30: On every sheet add iferror
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets

If ws.Visible = True Then ws.Select

‘Select all cells
Range(“A1”).Select
ActiveCell.CurrentRegion.Select

Dim r As Range
For Each r In Selection
If Left(r.Formula, 1) = “=” Then
If Left(r.Formula, 8) = “=IFERROR” Then
‘Do Nothing
Else
Dim myformula
myformula = Right(r.Formula, Len(r.Formula) – 1)
r = “=IfError(” & myformula & “,0)”
End If
End If
Next r

Next ws

Exit Sub
Errorhandler:
If Err.Number <> 0 Then
MsgBox “Something went wrong”
End If

End Sub

If you want to learn more about Excel VBA programming check out our 2-day Excel VBA course in New York City.

Excel Tutorial – Vlookup Multiple Columns To The Right With An Array Formula

In the process of teaching Excel classes, many students ask:

How can I write a single vlookup function that returns multiple values to the right, instead of a single value, which causes me to write many vlookup functions in many columns.

This tutorial does not teach vlookup, that feature is taught in the Introduction and Advanced classes. Here we focus on vlookup and array formulas. The purpose of this tutorial is to supplement the class notes.

When performing calculations in Excel, typically a single cell contains a formula with functions that return a single value. An array formula is different in that it will typically include many cells (rows or columns) and return many values. Where I would teach students to press the enter key to commit an edit, to create an array formula we are required to hold Ctrl+Shift+Enter.
To follow along with this example, please download a workbook with this link:

Vlookup Array Workbook

Though not required, I suggest when you open this workbook, click the Enable macros command which will create two windows and tile them side by side.
In this example, the idea is to write a single vlookup function on the VlookupOrders sheet. The single vlookup will find the order number on the Data sheet and return three values, the Company Name, the Country and the Sale.

Example Of Data In Vlookup Array Workbook

vlookup array formula purpose

1) On the VlookupOrders worksheet tab, select/highlight cells C2:E2
2) Create the following formula:
=VLOOKUP(B2,Data!B:F,{3,2,5},0)

vlookup array formula purpose

Note: For the third argument, the col_index_num, we are allowed to use as many values as needed. In this example I am trying to illustrate that as much as I can return three columns to three cells — in any sequence — we can choose any number of columns as needed. However, to vlookup four columns, in step 1, select/highlight four cells. Also note that we cannot simply add parentheses around the values of the col_index_num, use curly braces {} (squiggly brackets – typically found to the right of the letter P on your keyboard).

3) To convert this to an array formula, hold the Ctrl+Shift keys and press the Enter key. The array formula adds {} (curly braces) around the vlookup formula.

A final word of warning: Array formulas have drawbacks. One drawback is that array formulas recalculate a bit slowly over thousands of rows. A bigger drawback is that we have lost the ability to insert a new column within the arrayed formula range. To verify the loss of the ability to insert a column inside the array, on the VlookupOrders sheet, right click column D and left click the Insert command.