Autofilter - Process Each Row

[billgeo]

I want to do what seems to me a simple process to autofilter my sheet on a column and the process each filtered row in some manner.
I've tried and tried well over a dozen different ways found on Google and here using AutoFilter but they always result in some error. At this point I can't explain each error as there have been so many different ones. Maybe someone can post a simple general macro to do such.
Here is a basic example of what I'm looking for:
* A sheet with 20 rows and 6 columns of data
* The header row is row 3 (data in rows 4-20)
* I want to filter the sheet on column 1 = 100
* Then I want to process each displayed row using data in the displayed row.

Note: I'll be looping this process through several re-autofilers on column 1. My actually sheets has several thousand rows

Thanks for ANY insights!! as I'm going nuts not being able to get this simple idea to work.

Bill

 

[Norie]

Bill

How do you want to process each row?

What problems are you having doing that?

 

[billgeo]

Bill

How do you want to process each row?

What problems are you having doing that?

I'll be extracting data from several cells in each row, doing some magic on them and writing them out to another sheet.

What problems: No matter what example of use of Autofilter I ran into errors, many different errors. I tried so many different ways I can't pinpoint a specific issue.

 

[billgeo]

Note: I was able to successfully code a copy of the filtered rows to a new sheet. With this I could easily access each row as needed but that seemed like a lot of overhead since I'd have the filtered rows right there to access... if I could access them.

Bill

 

[Norie]

Bill

Can you post the code you used to do that?

 

[billgeo]

Bill

Can you post the code you used to do that?

Here is the code i have to copy the visible filtered rows to another sheet.
Would much rather just process the visible rows in sheet1 than having to copy them to another sheet first then process the rows.

Sub Copy_Filtered_Rows_To_Another_Sheet()
Dim ws As Worksheet
Dim rng As Range

    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A3").CurrentRegion
    On Error Resume Next
    rng.AutoFilter Field:=1, Criteria1:="100"
    rng.SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("DLN Ranges").Range("A1")
    rng.AutoFilter
End Sub

 

[Norie]

How about something like this?

Sub Copy_Filtered_Rows_To_Another_Sheet()
Dim ws As Worksheet
Dim rng As Range
Dim rngArea As Range
Dim rngRow As Range
Dim rngRowsToProcess As Range

    Set ws = Worksheets("Sheet1")
    Set rng = ws.Range("A3").CurrentRegion
    On Error Resume Next
    rng.AutoFilter Field:=1, Criteria1:="100"
    Set rngRowsToProcess = rng.SpecialCells(xlCellTypeVisible).EntireRow
    On Error GoTo 0

    ' check there are rows to process
    If Not rngRowsToProcess Is Nothing Then
        ' loop through each area to handle non-contiguous rows
        For Each rngArea In rngRowsToProcess.Areas
            For Each rngRow In rngArea.Rows
                ' process row
            Next rngRow
        Next rngArea
    End If

End Sub

 

[billgeo]

That looks encouraging!
So I understand correctly, an AREA is a contiguous group of rows? and the next AREA would be the next visible row, or group of visible rows, after the hidden row(s)?

I'll go give it try now.
Thanks!

 

[billgeo]

That looks encouraging!
So I understand correctly, an AREA is a contiguous group of rows? and the next AREA would be the next visible row, or group of visible rows, after the hidden row(s)?

I'll go give it try now.
Thanks!

Not quite.
When I put a debug.print to display the value in column 1 of each I'm getting values that are not visible in the filtered sheet.
However, this give me something to play with to see if I can make it work! THANKS

           For Each rngRow In rngArea.Rows
                Debug.Print rngRow.Row & "  " & rngRow.Cells(rngRow.Row, 1)
            Next rngRow

 

[Norie]

Using rngRow.Row in rngRow.Cells is not going to return a value from the row rngRow refers to.

Have a look at this little test.

Dim rngRow As Range

    Set rngRow = Rows(2)
    
    Debug.Print rngRow.Cells(rngRow.Row, 1).Address

It returns $A$3 rather than the expected $A$1.