VBA Blogs: Having a Row

This is the eleventh blog in the series about using ListObjects to manipulate Tables within an Excel workbook in VBA. Today we will be looking at all the commonalities between ListColumns and ListRows on the Item level.

ListColumns and ListRows are very similar. There are no properties that are unique specifically to ListRows. The methods and properties that apply to ListRows is strictly a subset of the properties that apply to ListColumns.

The methods and properties that are in common with ListColumns(Index) and ListRows(Index) ignoring the those covered last week are:

• Delete: deletes the specified column/row
• Index: returns the index number of the column/row referenced. This might be useful is because columns can actually be referred by their header names. Let’s look at an example to see how using the Index property might be useful. This is “MyTable”.

Running the following code to get the index value of column with the header "A" would result with a value of 1 as expected:

• An alternative use for Index is when data is being processed through loops and one would like to know the specific row or column that it would be in (detailed example to follow at the end)

• Range: This returns the Range object referenced by its address (and applications thereof). However, note this includes the Header of the column, because the header is considered part of the column.

Which row has number 5 in column “B”?

The following code has been run:

This is obviously a very simplistic example given that returning iCounter would have netted the same result. However, if subsections of records through nested loops are used, using Index to return what column or row that matches specific criteria can be very powerful.

This concludes ListRows so next time, let’s look at the things that are unique to ListColumns.