28. VBA Tips - Selecting Things Demo
In this VBA tip, we'll learn how to select various things on a worksheet:
Constants on the sheet
Formulas on the sheet
All blank cells
The last cell on the sheet
A whole table
The first heading in the table
The last heading
The last populated cell in the first column
The last populated cell in the last column
The second column in the table
The third row in the table
Here's a screen shot of what the demo is doing... in this case, selecting the third row of the table:
Here's the code we use to select things:
- We have 3 sub procedures:
- SelectingThings - the main control sub
- SelectAndWait - A sub that executes the command to select the cells, update the status, and wait for a second before the next one
- myWait - A procedure to wait for 1 second so that it's a useful demo.
- SelectingThings first runs selections for the whole sheet, then runs selections relating to the table.
- SpecialCells does a lot of the work for us here.
- We could use SpecialCells for the table selections too, but I have shown how to do it without.
- Apply the code to your table, and use however you want !
- In reality, there should be now need to select the ranges we want to operate on, but if you're not confident you have the right range, selecting it is a good thing to do to help you debug.
- When you have production code, comment out the range selecting as it slows down the procedure and is unnnecessary
NOTE: We have a named range called MyData in order for this to work... MyData contains the headings and the data in the table.
Training Video: Selecting Things Demo