• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: How to loop through table and display all column data in the row?

inddon

Member
Hello There,

I have a range table 'Table1' with some columns.
Columns: Status, First Name, Last Name, Gender, Age

I want to loop through the range table (row wise) and display all the column data in that row in a message. After displaying the message and before looping to the second row, the first column 'Status' in the first row should get a value 'Displayed'

How can this be achieved using VBA?

Thank & regards
Donald
 
Hi Inddon,

Try this code (to adapt) :
Code:
Sub Macro1()
Dim WS As Worksheet
Dim CT As Variant
Dim MSG As String

Set WS = Sheets("Sheet1")
CT = Range("A1").CurrentRegion
For I = 2 To UBound(CT, 1)
    MSG = ""
    For J = 1 To UBound(CT, 2)
        MSG = IIf(MSG = "", CT(I, J), MSG & " / " & CT(I, J))
    Next J
    MsgBox MSG
    WS.Cells(I, 1).Value = "Displayed"
Next I
End Sub
 
Hi Inddon,

Try this code (to adapt) :
Code:
Sub Macro1()
Dim WS As Worksheet
Dim CT As Variant
Dim MSG As String

Set WS = Sheets("Sheet1")
CT = Range("A1").CurrentRegion
For I = 2 To UBound(CT, 1)
    MSG = ""
    For J = 1 To UBound(CT, 2)
        MSG = IIf(MSG = "", CT(I, J), MSG & " / " & CT(I, J))
    Next J
    MsgBox MSG
    WS.Cells(I, 1).Value = "Displayed"
Next I
End Sub


Hello ThauThème,

Thanks for your code. I am looking for code where I can make use of the Table name and its components to gets the results. See attached file for reference

Regards
Don
 

Attachments

  • Loop Through Table.xlsx
    11 KB · Views: 9
Hi Don,

Maybe this way :
Code:
Sub Macro1()
Dim WS As Worksheet
Dim CT As Variant 'Cells Table
Dim MSG As String
Dim FR As Integer 'First Row
Dim FC As Byte 'First Coloumn

Set WS = Sheets("Sheet1")
CT = Range("Table1")
FR = Range("Table1")(1).Row
FC = Range("Table1")(1).Column
For I = 1 To UBound(CT, 1)
    MSG = ""
    For J = 1 To UBound(CT, 2)
        MSG = IIf(MSG = "", CT(I, J), MSG & " / " & CT(I, J))
    Next J
    MsgBox MSG
    WS.Cells(FR, 1).Value = "Displayed" 'or WS.Cells(FR, FC).Value = "Displayed"
    FR = FR + 1
Next I
End Sub
 
Hi Donald,

Please find attached file. Trust this help to resolve your query
 

Attachments

  • Loop Through Table.xlsm
    21.2 KB · Views: 42
Hi Donald,

Please find attached file. Trust this help to resolve your query


Hello Simayan,

Thank you for your code, it works.

The below code I was looking for. A refined code where table realated reference is used.

Code:
Sub ListTableColumn()
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet
Dim lr As Excel.ListRow
Dim lc As Excel.ListColumn
Dim MsgString As String

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set lo = ws.ListObjects("Table1")

For Each lr In lo.ListRows
  For Each lc In lo.ListColumns
  MsgString = MsgString & " " & Intersect(lr.Range, lo.ListColumns(lc.Name).Range).Value
  Next lc
  MsgBox MsgString
  MsgString = ""
Next lr
End Sub

Regards
Don
 
The Code I have shared works in same way but it also shows column headers which can be removed if you want.

Separately currently we are putting input that which table needs to be looped; which can be replaced either by combo box selection or by providing an array which will contain the table names ( named ranges)

Please share your views
 
The Code I have shared works in same way but it also shows column headers which can be removed if you want.

Separately currently we are putting input that which table needs to be looped; which can be replaced either by combo box selection or by providing an array which will contain the table names ( named ranges)

Please share your views

Hello Simayan,

Both code works. It depends on individual developer & their approach in writing code.

Appreciate you help, your code gave me some direction in achieving the results in a different way (by using range table references). I am sure there would be better ways to further refine this code and make it simple and shorter.

I am all learning :)

Regards
Don
 
Back
Top