
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).SelectThis 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 SubNote: 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).SelectWith 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 NowNext up:
- Mobilising SharePoint 2013
- Becoming a great workplace trainer starts with three words (Part 2)
- 3 programming tips in Visual Basic
- Implementing security in SQL Server 2014
- Borders and shading in Microsoft Word 2010
- Reliability Monitor in Windows 8
- Communicating clearly to save time and money
- TechEd 2014 – Australia’s largest Microsoft IT exhibition is evolving!
- Sorting made easy with custom lists in Excel
- 4 tips to make you a Windows 8.1 pro
Previously
- How to set up a Windows 7 and 8 HomeGroup
- Help! I typed in the wrong dimensions for my InDesign document!
- On being a Professional Development trainer
- Hyper-V – Enhanced
- Use slicers to filter table data in Microsoft Excel
- Implementing Big Data Solutions in SQL Server 2014
- VBA Excel: Finding the last row of a worksheet (Part 1)
- EAs and PAs: Asking your boss the right questions
- How to create fillable forms in Microsoft Word
- The Exchange Admin Center (EAC) of Exchange 2013 – It’s new!