Looping through all rows in a table column Excel-VBA

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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  amp    contains    amp  tRows  amp    rows and    amp  tCols  amp    columns    vbInformation  End Sub   If you need to use the header row  instead of using tbl DataBodyRange just use tbl Range

User · Answer

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  amp   - Yes      amp  Range  myTable Yes    i  Value  amp  Chr 10          Table2text  Table2text  amp   - No       amp  Range  myTable No    i  Value  amp  Chr 10          Table2text  Table2text  amp   - Maybe    amp  Range  myTable Maybe    i  Value  amp  Chr 10   amp  Chr 10      Next i     Finalize return value    Table2text   Table2text  amp  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

User · Answer

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

User · Answer

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

[vba] Looping through all rows in a table column, Excel-VBA

Examples related to vba

Examples related to excel

Examples related to excel-2010