Loop Through Rows of Filtered Table

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,570
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I was struggling to find a VBA solution that I could extract data from all the rows of a filtered table. With my failures, I resorted to a Google search and found what I thought might/should work, but I clearly don't know enough to have realized it truly wasn't the solution. I present you my code:

VBA Code:
Sub process_segments()
    Dim filter_rng As Range
    Dim drow as integer
    Dim segnum as integer
    Dim segtype as string
    Dim segtxt as string

    With ws_form
        ws_segments.AutoFilterMode = False
        With ws_segments.Range("A1")
            .AutoFilter field:=1, Criteria1:=zone
            .AutoFilter field:=2, Criteria1:=corridor
            '.AutoFilter field:=4, Criteria1:=segment
        End With
        With ws_segments
            Set filter_rng = .Range("A:F")
            drow = 9
            For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)
                segnum = .Range("C" & rw.Row).Value
                segtxt = .Range("E" & rw.Row).Value
                segtype = .Range("F" & rw.Row).Value
                'copy data to another worksheet first data line is cell A2
                ws_form.Range("A" & drow).EntireRow.Insert
                ws_form.Range("G" & drow).Value = segtype
                ws_form.Range("H" & drow).Value = "[" & segnum & "]"
                ws_form.Range("J" & drow).Value = segtxt
                drow = drow + 1
            Next rw
        End With
    End With
End Sub

The code I thought would step through each row of the filtered data, assigning values extracted from that current row to variables, to be later placed in a data holding area in another worksheet. This is not happening.
What is happening is it only refers to data in row 1, the header row so the values of segnum, segtxt and segtype are always the same and equal to the header values in row 1 respectively.

Essentially, I need my loop to step through each visible row in the table with the exception of row 1. I'm not certain how to create that loop.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
You are looping through all the visible cells, not the visible rows. That means that you will refer to say A1 in the first loop, then B1, then C1 and so on. Alter the filter_rng to only refer to one column, and it's generally best to specify the Cells property:

VBA Code:
            Set filter_rng = .Range("A:A")
            drow = 9
            For Each rw In filter_rng.SpecialCells(xlCellTypeVisible).Cells
 
Upvote 0
Solution
Yeah, that's the nudge I needed. Thanks as always Rory.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top