See previous article: Excel VBA Selection and Navigation Techniques
In the last article I showed you how to make some basic navigation and selection techniques using the macro recorder. In this article I’ll show you how you can navigate and select when the data is more complex. Blank cells can be difficult to deal with if you don’t know how. I’ll take you through some of the methods I use when coding in Excel VBA.
Let’s start with some simple selection techniques like selecting a column or a row.
This selects column A
Columns(“A:A”).Select
This selects column A through to column E
Columns(“A:E”).Select
For rows use
Rows(“1:5”).Select
Navigating using the Range Object
Starting with selecting a single cell
Range(“A5”).Select
NB** We can also use Cells(5,1).Select for this purpose. This code refers to a cell by its row number first and then the column number second. It’s the old style of cell referencing. It’s called R1C1 cell referencing style. A lot of VBA code uses this style of cell referencing. I’ll discuss this later in another post.
Selecting multiple cells
Range(“A5:A10”).Select
or
Range(Range(“A5”),Range(“A10”)).Select
Selecting two different groups of multiple cells
Range(“A5:A10,D5:D10”).Select
Selecting a named range called Data
Range(“Data”).Select
Using Union to Select Data
Selecting two different groups of multiple cells using Union
Union(range(“A5:A10”),range(“D5:D10”)).Select
Selecting two different groups of multiple cells using Union and Named Ranges
Union(range(“Data1”),range(“Data2”)).Select
Now let’s look at some more Excel VBA to select a list of data.
Selecting a List
Ensure your activecell is somewhere in the list
Activecell.CurrentRegion.Select
If you know where the list starts
Range(“A3”).CurrentRegion.Select
Selecting and Formatting a Column with Blank Cells
Now here’s where it can get tricky. Unless you know the specific height and width of the list, the previous examples won’t help you select an entire row or column of data in a list. Its even harder when there are scattered blank cells. That’s where I use CurrentRegion. First of all just knowing that you can count the rows and columns in a selection can help.
This will count the rows in a selection
Selection.Rows.Count
This will count the columns in a selection
Selection.Columns.Count
Now that you know that, we can easily count the rows or columns in a list. Lets look at an example. If you wanted to make all the cells in the first column of a list bold when there are blank cells you could use the following macro.
Sub FormatColumnToBold()
‘Create the variable to store the row count
Dim RowCount As Integer
‘Count the rows in the list
RowCount = Range(“A1”).CurrentRegion.Rows.Count
‘Format the first column to bold
Range(Range(“A1”), Cells(RowCount, 1)).Font.Bold = True
End Sub
Selecting and Formatting a Column with Blank Cells and a Header Row
If your list has a header row we just need to make a couple of little changes to the code to accommodate the extra row.
Sub FormatColumnToBold()
‘Create the variable to store the row count
Dim RowCount As Integer
‘Count the rows in the list and add 1 for the header row
RowCount = Range(“A1”).CurrentRegion.Rows.Count + 1
‘Format the first column to bold
Range(Range(“A2”), Cells(RowCount, 1)).Font.Bold = True
End Sub
If you look closely I’ve added 1 to the RowCount to accommodate for the header row and I’ve also started from Range(“A2”) in the last line of code to exclude the header row from becoming bold.
In the next post I’ll show you how to work with blank cells in a list when you don’t know where the list starts. Stay tuned…
Need to learn Excel VBA – Click here
