VBA Tutorial 3: Intermediate

Topics covered in this Tutorial:

• Last Used Row / Column
• Working on Multiple Workbooks & Worksheets

Last Used Row / Column

When accessing an excel file with large records, you may want to find the last used row or column to add records to the file or read every cell value line by line.

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastColm = Cells(1, Columns.Count).End(xlToLeft).Column

Let’s dissect the code above.
Cells(Row.Count, 1) tells the computer to count the rows in Column 1.
End(xlUp).row indicates the method the computer uses to find the last row. The computer goes to the end of the file and then [Ctrl + Up] to determine the last used row.
Select cell A1. Press [Ctrl+Down] twice. Then press [Ctrl + Up] once

Let’s do a simple exercise to print every cell value row by row in the immediate window.

• Determine the last used row using the code above.
• Loop from 1 to lastRow using a for loop
• Extract the values of cells in each row
• Print the values to immediate window

Working on multiple workbooks & sheets

The code we wrote in the previous section is written under Sheet1. However, this code when placed under Sheet1 will not work for data in Sheet2.

There are 2 workarounds:
1) Place the code in Sheet2
2) Refer to Sheet2 while the code is still in Sheet1.

To execute method 2, we need to be able to introduce the current workbook and worksheets as variables.

Dim workbookName as Workbook
Set workbookName = ThisWorkbook

Dim worksheetName as Worksheet
Set worksheetName = workbookName.sheets("SheetName")

Here’s what we are going to do, explained in simple English:

• Define Workbook and Worksheets (Sheet1 & Sheet2)
• Determine lastRow for Sheet2
• Loop from 1 to lastRow (using for Loop)
• Extract values for each row in Sheet2
• Print values in Immediate Window

Did you notice that to refer to cells in Sheet2 we just had to add ws2 as a prefix?

lastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print ws2.Cells(i, 1).Value

Next: Tutorial 4 String Manipulation

Previous: Tutorial 2 Basics