CODEWITHSUNDEEP
vbaexcelloopsexcel-2010
Deepak
Deepak

Reputation: 473

Getting cell value by looping through entire column

I would like to apply filter on a table based the values presented in cells A1:AG1. But the problem is when my data gets updated, sometimes i have values in other cells like AH,AI etc., The values available only on first row.

So i tried to add a loop for every cell, but it is not working. How to change my code to loop through every column in a single row. Help me

Dim ws As Worksheet
Dim str2 As Variant
Dim arr2() As String
Dim j As Long
Dim rng As Range

Set ws = Sheets("Main")
Set Tbl = Sheet2.ListObjects("DataTable")
Set rng = Range("A1:AG1") 'Need to change

j = 1
For Each cell In rng
    str2 = cell.Value
    ReDim Preserve arr2(j)
    arr2(j) = str2
    j = j + 1
Next cell

Tbl.Range.AutoFilter Field:=12, Criteria1:=arr2, Operator:=xlFilterValues

End sub

Upvotes: 0

Views: 1789

Answers (2)

Sérgio Mesti Samorano
Sérgio Mesti Samorano

Reputation: 1

I do not know if I understood your question well, but just replace:

Set rng = Range("A1:AG1")

With:

Set rng = ws.range(ws.cells(1 , 1) , ws.cells(1 , ws.Cells.Columns.Count).End(xlToLeft).Column

Upvotes: 0

Xabier
Xabier

Reputation: 7735

How about something like below:

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim arr2() As String
Dim i As Long

Set ws = Sheets("Main")
Set ws2 = Sheets("Sheet2")
Set Tbl = ws2.ListObjects("DataTable")

LastCol = ws2.Cells(1, ws2.Columns.Count).End(xlToLeft).Column
'above will give you the last column number in row one of sheet ws2
ReDim Preserve arr2(1 To LastCol) 're-size the array
'Set rng = Range("A1:A" & LastCol) 'set your range from column 1 to last


For i = 1 To LastCol 'loop through columns
    arr2(i) = ws2.Cells(1, i).Value 'add value to array
    'above number 1 represents Row 1, and i will loop through columns
Next i

Tbl.Range.AutoFilter Field:=12, Criteria1:=arr2, Operator:=xlFilterValues
'above will filter table column 12 with array values?

Upvotes: 1

Related Questions