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
