Set all cells in a table column to a specific value

Question

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.

David Zemens · Answer 1 · 2014-05-15T22:04:17.533

31

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.

edited May 15 '14 at 22:04

answered Jul 10 '13 at 15:55

David Zemens

53,033
11
81
130

Should look like: tCols = .Columns.Count... Cleanest solution in my opinion – JoeFox May 15 '14 at 21:27
What is the appropriate way to loop and reference the table cells with this code? – Dec 30 '15 at 19:35
@ScottBeeson you can iterate over the `tbl.DataBodyRange.Cells` collection, or by index: `tbl.DataBodyRange(1, 1)`, etc. – David Zemens Dec 30 '15 at 19:36
so like `For i = 1 To tbl.ListRows.Count` `tbl.Range("G" & i).value = "test"`? – Dec 30 '15 at 19:39
I would do `For i = 1 To tbl.ListRows.Count : tbl.ListRows(i, 7).Value = "test"` (assuming you want the seventh column in the table, regardless of what *letter* column that happens to be, otherwise, modify as needed). @ScottBeeson Otherwise, if you have trouble implementing, please do ask a new question of your own. Cheers. – David Zemens Dec 30 '15 at 19:49

score 28 · Answer 2 · answered Oct 14 '15 at 14:33

28

Assuming that your table is called 'Table1' and the column you need is 'Column' you can try this:

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

answered Oct 14 '15 at 14:33

George Lupu

282
1
4
8

2

Thanks! very simple and elegant solution to the problem, saved me a bunch of coding with other more complex solutions. – Angel Feb 08 '16 at 21:21
2

The quintessential answer. Should be the accepted/highest votes (and expect it will be over time if users keep noticing it later on). Much better than the lame detours answers at other sites took me on. – JeopardyTempest Aug 17 '18 at 02:15
Wonder if you're working on a really big table, if it may end up being a tiny bit more efficient to retrieve the column once and then work from that, rather than keep using the range. so extra lines... looks like it'd be myColumn = Range("Table1[Column]") before the for loop (and for good measure something like dim myColumn as Variant??? Looks to be reporting as some kind of array? Or is there a more precise data type I'm missing?)... and then myColumn(i) = "PHEV" in the code. But it's likely nothing of importance in many situations? – JeopardyTempest Aug 17 '18 at 02:29

score 12 · Answer 3 · answered Aug 21 '17 at 04:56

12

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

answered Aug 21 '17 at 04:56

SnitkaSirk

129
1
2

LS_ᴅᴇᴠ · Accepted Answer · 2014-06-19T00:12:36.273

10

You can search column before assignments:

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

edited Jun 19 '14 at 00:12

answered Jul 10 '13 at 16:01

LS_ᴅᴇᴠ

10,823
1
23
46

1

Line 3, you have a comma instead of a dot to access the value of the cell. – esylvestre Jun 18 '14 at 15:42
2

Good answer. I wish Excel were more semantic... why can't I just refer to the column like table["column name"] like a dictionary in python or JSON? It sort of does this with other table operations, e.g. referring to other column values in the same row. – william_grisaitis Jun 18 '14 at 21:05
@grisaitis Because that would be too sensible. Microsoft likes confusion. – ATL_DEV Jul 29 '21 at 16:15

score 2 · Answer 5 · answered Jul 10 '13 at 15:58

If you know the header name, you can find the column based on that:

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

+1. If you're setting all cells in a column to a value, there is absolutely no reason to loop through the range to do so. Simply set the range to the value you want, as @joseph4th does here. I would also declare a worksheet object so that you don't need to use ActiveSheet. — Jon Crowell, Jul 10 '13 at 19:00

score 2 · Answer 6 · answered Jun 03 '16 at 07:48

You can loop through the cells of any column in a table by knowing just its name and not its position. If the table is in sheet1 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 loop through the data values only, excluding the header row and the totals row. It is not necessary to specify the number of rows in the table.

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

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

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

score 1 · Answer 7 · edited Sep 07 '15 at 17:17

1

I came across the same problem but no forum could help me, after some minutes I came out with an idea:

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

This will return you the number.

edited Sep 07 '15 at 17:17

durron597

31,968
17
99
158

answered Sep 02 '15 at 07:50

Javier

11
1

score 0 · Answer 8 · answered Jul 10 '13 at 15:42

You can find the last column of table and then fill the cell by looping throught it.

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

score 0 · Answer 9 · answered Apr 04 '19 at 13:54

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

I convert a table into text using the values of some named columns (Yes, No, Maybe) within the named Excel table myTable on the tab mySheet using the following (Excel) VBA snippet:

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 a range rngTab over which we loop. The trick is to use Range("myTable[col]")(i) to extract the entry of column col in row i.

score 0 · Answer 10 · answered Nov 17 '21 at 13:38

I modified a little bit SnitkaSirk's answer. It's simple way of looping through listobject cells, for example in a column:

Dim cell As Range
For Each cell In ListObjects("NameOfTable").ListColumns("HeaderNameOfColumn").DataBodyRange
    Debug.Print x
Next

Set all cells in a table column to a specific value

10 Answers10

Linked