Iterate over all rows and columns in table with VBA

Question

I have a table in a worksheet that I want to iterate over and change the value of using a function I had set up. The function just calls on multiple Replace functions to change the strings.

My question is how do I iterate over the values in the table only?

I was able to do it with columns using the following code:

For Each cell In Sheets("RawData").ListObjects("RawTable").ListColumns("REQUESTNAME").DataBodyRange.Cells
    cell.Value = decodeEnt(cell.Value)
Next

But how do I modify that code to make it iterate through the rows as well? I tried using a combination of ListRows and ListColumns, but didn't know where to go afterwads. Here is where I got to before I was unsure:

Dim listObj As ListObject
Set listObj = Sheets("RawData").ListObjects("RawTable")

For Each tableRow In listObj.ListRows
    For Each tableCol In listObj.ListColumns
        ' Would using intersect work here?
        ' listObj.Cell.Value = decodeEnt(cell.Value)
Next

Any help is appreciated.

I was searching for a variant solution. Came across one at: https://stackoverflow.com/questions/12495678/ — tutu, Dec 09 '18 at 20:08

David Zemens · Accepted Answer · 2014-01-21T19:21:40.873

5

I think you can't use a variant iteration like that, you have to use indexed iteration. Something like this (untested):

Dim listObj As ListObject, r%, c%
Set listObj = Sheets("RawData").ListObjects("RawTable")

For c = 1 To listObj.ListColumns.Count
    For r = 1 To listObj.ListRows.Count
        listObj.DataBodyRange.Cells(r, c).Value = decodeEnt(listObj.DataBodyRange.Cells(r, c).Value)
    Next
Next

edited Jan 21 '14 at 19:21

answered Jan 21 '14 at 17:33

David Zemens

53,033
11
81
130

1

What does the hashtag # mean? EDIT: Nvm, I see now that it's a shorthand way of dimensioning it as a double. – Mo2 Jan 21 '14 at 18:45
1

Your solution works. There were a few syntax errors which I fixed in your post, but everything else was fine. Thanks! – Mo2 Jan 21 '14 at 19:21
2

+1. I always forget about the `.DataBodyRange` (would've caught that if I had tested the code but I was just going from memory :)) – David Zemens Jan 21 '14 at 19:22

score 5 · Answer 2 · answered Jan 21 '14 at 20:04

5

Dim Cell as Range

For Each Cell in listObj.DataBodyRange
...

answered Jan 21 '14 at 20:04

Excel Developers

2,785
2
21
35

That works too. Even shorter than the previous answer. Thanks – Mo2 Jan 21 '14 at 20:17

Iterate over all rows and columns in table with VBA

2 Answers2