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

Sub AllRowValues()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRow
Debug.Print Cells(i, 1).Value
Next i
End Sub

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

Sub Sheet2RowValues()
Dim wb As Workbook
Dim ws2 As Worksheet
Set wb = ThisWorkbook
Set ws2 = wb.Sheets("Sheet2")
lastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lastRow
Debug.Print ws2.Cells(i, 1).Value
Next i
End Sub

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s