Looping all rows in a table column, Excel-VBA

Question

I am currently working on a dataset that is formatted as a table with headers. What I need to do is loop through all the cells in a specific column and change the contents. Thanks to MSDN research, I came up with the following for the loop

for i = 1 to NumRows Cells(i,23).Value = "PHEV" next i 

Thus, this will change all the cells in column 23 to "PHEV". However, I am not creating a table in which I work on my own, so I cannot guarantee that the column of interest will be column 23.

I would 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 this syntax is incorrect, but I hope it illustrates my purpose sufficiently.

Answer 1

You can find the column before the assignment:

 Dim col_n as long for i = 1 to NumCols if Cells(1, i).Value = "column header you are looking for" Then col_n = i next for i = 1 to NumRows Cells(i, col_n).Value = "PHEV" next i 

Answer 2

If this is actually a ListObject (Insert Table from the ribbon) table, you can use the table .DataBodyRange object to get the number of rows and columns. This ignores the title bar.

 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 a title bar, instead of using tbl.DataBodyRange just use tbl.Range .

Answer 3

Assuming your table is called "Table1" and the desired column is "Column", you can try the following:

 for i = 1 to Range("Table1").Rows.Count Range("Table1[Column]")(i)="PHEV" next i 

Answer 4

If you know the name of the header, you can find a column based on this:

 Option Explicit Public Sub changeData() Application.ScreenUpdating = False ' faster for modifying values on sheet Dim header As String Dim numRows As Long Dim col As Long Dim c As Excel.Range header = "this one" ' header name to find Set c = ActiveSheet.Range("1:1").Find(header, LookIn:=xlValues) If Not c Is Nothing Then col = c.Column Else ' can't work with it Exit Sub End If numRows = 50 ' (whatever this is in your code) With ActiveSheet .Range(.Cells(2, col), .Cells(numRows, col)).Value = "PHEV" End With Application.ScreenUpdating = True ' reset End Sub 

Answer 5

I ran into the same problem, but not a single forum helped me, after a few minutes I came up with the idea:

 match(ColumnHeader,Table1[#Headers],0) 

This will return the number to you.

Answer 6

You can find the last column of the table, and then fill the cell by executing its cycle.

 Sub test() Dim lastCol As Long, i As Integer lastCol = Range("AZ1").End(xlToLeft).Column For i = 1 To lastCol Cells(1, i).Value = "PHEV" Next End Sub 

Answer 7

You can scroll the cells of any column in the table, knowing only its name, and not its position. If the table is in sheet 1 of the workbook:

 Dim rngCol as Range Dim cl as Range Set rngCol = Sheet1.Range("TableName[ColumnName]") For Each cl in rngCol cl.Value = "PHEV" Next cl 

The code above will cycle through only the data values, with the exception of the header row and summary row. There is no need to specify the number of rows in the table.

Use this to find the location of any column in a table by column name:

 Dim colNum as Long colNum = Range("TableName[Column name to search for]").Column 

This returns the numeric position of the column in the table.

Answer 8

Assuming your table is called "Table1" and your column is called "Column1", then:

 For i = 1 To ListObjects("Table1").ListRows.Count ListObjects("Table1").ListColumns("Column1").DataBodyRange(i) = "PHEV" Next i 

Answer 9

Since none of the above answers helped me with my problem, here is my solution to extract a specific (named) column from each row.

I convert the table to text using the values ​​of some named columns ( Yes , No , Maybe ) in the Excel named table myTable on the mySheet tab using the following (Excel) VBA fragment:

 Function Table2text() Dim NumRows, i As Integer Dim rngTab As Range Set rngTab = ThisWorkbook.Worksheets("mySheet").Range("myTable") ' For each row, convert the named columns into an enumeration For i = 1 To rngTab.Rows.Count Table2text= Table2text & "- Yes:" & Range("myTable[Yes]")(i).Value & Chr(10) Table2text= Table2text & "- No: " & Range("myTable[No]")(i).Value & Chr(10) Table2text= Table2text & "- Maybe: "& Range("myTable[Maybe]")(i).Value & Chr(10) & Chr(10) Next i ' Finalize return value Table2text = Table2text & Chr(10) End Function 

We define the rngTab range by which we loop. The trick is to use Range("myTable[col]")(i) to retrieve the col column entry in row i .