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:

Explanation

1. We have 3 sub procedures:
   1. SelectingThings - the main control sub
   2. SelectAndWait - A sub that executes the command to select the cells, update the status, and wait for a second before the next one
   3. myWait - A procedure to wait for 1 second so that it's a useful demo.
2. 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.
3. Apply the code to your table, and use however you want !
4. 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.
5. 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.

Download sheet to practise how to Select things in Excel Using VBA

Training Video: Selecting Things Demo