Clever Geek Handbook

Select entire column in table using Excel VBA

I have a table in an excel sheet and I want to select the entire first row. Is there an easier / faster way to reference the table than the usual

 Range("A2").End(xlDown).Select

method? It seems that using a table I should get an easier way to access the data. Thank.

+4


source to share


3 answers


With these codes you can select different parts of the table.

Whole table:
ActiveSheet.ListObjects("Table1").Range.Select

Table Header Title:
ActiveSheet.ListObjects("Table1").HeaderRowRange.Select

Table data:
ActiveSheet.ListObjects("Table1").DataBodyRange.Select

Third column:
ActiveSheet.ListObjects("Table1").ListColumns(3).Range.Select

Third column (data only):
ActiveSheet.ListObjects("Table1").ListColumns(3).DataBodyRange.Select



Select row 4 of the table data:
ActiveSheet.ListObjects("Table1").ListRows(4).Range.Select

Select the third heading:
ActiveSheet.ListObjects("Table1").HeaderRowRange(3).Select

Select the data item in row 3, column 2:
ActiveSheet.ListObjects("Table1").DataBodyRange(3, 2).Select

subtotals:
ActiveSheet.ListObjects("Table1").TotalsRowRange.Select

For a complete guide to tables, see VBA Guide to Excel ListExject Tables .

+22


source


This is the shortest way I know:

Rows(1).Select


Here you can plunge into an example.

0


source


The above seems to only work if it is an actual Excel table object. If you only have rows and columns of data with headers in a worksheet and it is not an actual Excel table object, it will not work and you have to use the following code.

We select a range from A2 (A1 is the header row of our column and we don't want to clear the header), ctrl to the right, then ctrl to down to find the last record in the sheet. We then clear the rows and columns on the sheet.

Sub clear_table()
    Range("A2", Range("A2").End(xlToRight).End(xlDown).Address).Clear
End Sub
0


source






More articles:


All Articles