I'm currently working on a data set which is formatted as a table, with headers. What I need to do is cycle through all cells in a specific column and change the contents. Through research on MSDN I came up with the following for loop
for i = 1 to NumRows
Cells(i,23).Value = "PHEV"
next i
So this would change all the cells in column 23 to read "PHEV". However, I do not build the table I'm working with myself, so I can't guarantee that the column I'm interested in will be column 23.
I'd like to implement something similar to the following:
for i = 1 to NumRows
Cells(i,[@[columnHeader]]).Value = "PHEV"
next i
Of course, I know that that syntax is incorrect, but hopefully it sufficiently illustrates my goal.
If this is in fact a ListObject
table (Insert Table from the ribbon) then you can use the table's .DataBodyRange
object to get the number of rows and columns. This ignores the header row.
Sub TableTest()
Dim tbl As ListObject
Dim tRows As Long
Dim tCols As Long
Set tbl = ActiveSheet.ListObjects("Table1") '## modify to your table name.
With tbl.DataBodyRange
tRows = .Rows.Count
tCols = .Columns.Count
End With
MsgBox tbl.Name & " contains " & tRows & " rows and " & tCols & " columns.", vbInformation
End Sub
If you need to use the header row, instead of using tbl.DataBodyRange
just use tbl.Range
.