VBA Excel: Finding the last row of a worksheet (Part 2)

 May 26, 2014

In my last blog post, VBA Excel: Finding the last row of a worksheet (Part 1), I explained how to use the control arrows to find the last row of a worksheet in VBA Excel. Here are 3 more keyboard shortcut techniques that you can also try for yourself when working in VBA Excel. Shortcut #1: Start at the bottom and go up Rather than starting at the top and going down, start at the bottom and go up. Go to row 65,536 (for .xls files in Excel 1997–2003) or row 1,048,576 (for .xlsx files in Excel 2007 onwards) and then go upward using Ctrl+Up Arrow on your keyboard. The first two lines of code would look like this (the rest of the code would be the same):
 Range(“A1048576”).Select Selection.End(xlUp).Select
This does assume that the bottom row of the data won’t be blank in Column A. Shortcut #2:Use Ctrl * Ctrl * (or Ctrl+Shift 8) selects what is known as the ‘current region.’ Give it a test by clicking on a single cell inside some data and seeing how it expands to select the entire ‘block’ of information. It basically stops at a blank row, a blank column, or the edge of the sheet. So we could click somewhere we know there is data and then click Ctrl *. The whole area would then be selected and we could use the .Address property to take note of the whole area and store it in a variable. Here’s the code; I’m using a variable called WholeArea (again, as I mentioned in my last post, that’s a made up name).
Sub FlexibleSort2() Range(“A4”).Select Selection.CurrentRegion.Select – this is the Ctrl * WholeArea = Selection.Address ActiveWorkbook.Worksheets(“Payroll”).Sort.SortFields.Clea ActiveWorkbook.Worksheets(“Payroll”).Sort.SortFields.Add Key:=Range(“A5:A65536“) _ , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(“Payroll”).Sort .SetRange Range(WholeArea) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
Note: I’ve used a work-around in this example where I specified “A5:A65536.” I could have done additional lines of code to find out the last row to use as the ‘Row to Sort by,’ but I want to keep it simple. The concept is the important thing here. This one assumes that there aren’t any entirely blank rows or entirely blank columns in the data, because if there were, Ctrl * would stop at that gap. Shortcut #3: Use Ctrl End Ctrl End takes you to the bottom right hand corner of the ‘used area’ of the sheet, not the whole sheet. That’s almost always a lot bigger. The first two lines of code would look like the below. The rest of the code would be the same as the Ctrl Down Arrow example.
Range(“A1”).Select ActiveCell.SpecialCells(xlLastCell).Select
With Ctrl End, it looks for the bottom right hand corner of cells that are ‘non pristine’ (that have had something in them at any time in the past). Clearing out data from a cell doesn’t make it ‘pristine’ again. The only way to make a cell ‘pristine’ is to delete the cell or cells, preferably the whole row or column, and then save the file (Excel doesn’t actually recalculate the ‘pristine area’ until you save the file.) So there you go! Many different ways to find the bottom row of a sheet of data. Hope that helps you in your VBA! By the way, there are properties that you can use to find out the information above directly, without having to move the selection around. For example, Range(“A1”).CurrentRegion.Address will return the area of the current ‘block of information’ around the cell A1 regardless of where the selection is. The answer would look something like this: $A$1:$C$18. Using this property, you could do all of what we have talked about and it doesn’t matter where the selection is, which is very cool! However, this is a whole new topic, which I may cover in a future blog.

How do your Excel skills stack up?   

Test Now  

About the Author:

Matthew Goodall  

Matthew is a qualified Microsoft Office Specialist, Microsoft Certified Applications Specialist and a Microsoft Certified Trainer with over 11 years of hands-on experience in a training facilitation role. He is one of New Horizons most dynamic instructors who consistently receives high feedback scores from students. Matt enjoys helping students achieve real professional and personal growth through the courses he delivers. He is best known for creating “fans” of students, who regularly request him as an instructor for any future courses they undertake at New Horizons.

Read full bio
top