CODEWITHSUNDEEP
excelvba
J. Doe
J. Doe

Reputation: 199

VBA Iterate through a table

I am trying to iterate through a filtered table, and print the values of 3 columns. I want them to print with each row as a set (supplier, plant, price). This is the code I have so far.

For Each Row In Union(Range("TblRawData[Supplier]"), Range("TblRawData[plant]"), Range("TblRawData[price]")).Rows
    If Row.EntireRow.Hidden = False Then
          Debug.Print Row.Value
    End If
Next Row

This code prints all the suppliers, then all the plants, then all the prices. Instead of each row being a set.

Code Results:        Desired Result:
supplier1            supplier1, plant1, $1.50
supplier2            supplier2, plant2, $2.00
supplier3            supplier3, plant3, $3.00
plant1
plant2
plant3
$1.50
$2.00
$3.00

Upvotes: 2

Views: 3705

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

This approach worked for me using ListRows and simple variable construction.

(forgive a, b, c variable names in this case)

Option Explicit

Sub printRows()

    Dim rawData As ListObject
    Set rawData = Worksheets("Sheet1").ListObjects("TblRawData") 'change sheet name as needed

    Dim lr As ListRow
    For Each lr In rawData.ListRows

        If lr.Range(1, 1).EntireRow.Hidden = False Then

            Dim a As String, b As String, c As String
            a = lr.Range(1, rawData.ListColumns("Supplier").Index).Value
            b = lr.Range(1, rawData.ListColumns("plant").Index).Value
            c = Format(lr.Range(1, rawData.ListColumns("price").Index).Value, "$#,###.00")

            Dim output As String
            output = a & ", " & b & ", " & c

            Debug.Print output

        End If

    Next

End Sub

Upvotes: 5

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Debug.Print implicitly writes a carriage return / "new line". You'll want to use (;) control characters here, inside the loop, to prevent it - and with a comma (,) you can align the values with tabulations (no need to actually output a comma):

Debug.Print Row.Value,; '<~ that semicolon is important!

And at each new Row:

Debug.Print '<~ no semicolon will append the carriage return so next iteration outputs to a new line

In other words:

For Each Row In Union(Range("TblRawData[Supplier]"), Range("TblRawData[plant]"), Range("TblRawData[price]")).Rows
    If Row.EntireRow.Hidden = False Then
          Debug.Print Row.Value,;
    End If
    Debug.Print
Next Row

Should output something like this:

supplier1      plant1    $1.50
supplier2      plant2    $2.00
supplier3      plant3    $3.00

Upvotes: 3

Related Questions