VBA Iterate through a table

Question

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

Scott Holtzman · Accepted Answer · 2020-04-23 17:59:03Z

5

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

answered Apr 23, 2020 at 17:59

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BigBen Over a year ago

Upvoted. Yes, in OP, iteration was cell by cell down the columns... So yeah, this is the way to do it and scrap Union altogether.

J. Doe Over a year ago

works like a charm. thank you! just curious, is there a reason you declared the variables inside the loop?

Mathieu Guindon Over a year ago

@J.Doe doing that has no effect on the scoping of variables (Dim is not an executable statement), but declaring variables right where you're using them makes it 1) much harder to end up with unused variables and 2) much easier to later take that loop body and refactor it into its own scope.

Scott Holtzman Over a year ago

@J.Doe - what Mathieu Guindon said :)

Scott Holtzman Over a year ago

@MathieuGuindon - I would also add it makes it easier to know exactly what the variable type is - because you are using it right where you declare it.

Mathieu Guindon · Accepted Answer · 2020-04-23 16:26:08Z

3

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

edited Apr 23, 2020 at 16:26

answered Apr 23, 2020 at 16:22

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

8 Comments

Mathieu Guindon Over a year ago

@ScottHoltzman #FunFact: the behavior of Debug.Print mimicks PRINT statements of the olden days! Was super useful when your entire UI was made with ASCII characters =)

NickSlash Over a year ago

I get a run-time error when I try this, Debug.Print Row.Value, ; (the IDE adds a space between comma and semi-colon)

Mathieu Guindon Over a year ago

@NickSlash hm, works exactly as expected here (the VBE adding spaces is normal)

BigBen Over a year ago

Potential problem... if those table columns are contiguous, then Debug.Print Row.Value will throw a type mismatch, because Row will be a multi-cell range. I'd use .Cells on the end of Union.

Scott Holtzman Over a year ago

@J.Doe - Yes. It does, because if you read it, it's doing exactly what is asked of it :). I will try to write up ListObject approach below.

|

Collectives™ on Stack Overflow

VBA Iterate through a table

2 Answers 2

5 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related