Select all visible cells after filter with macro I apply a filter to column C by

Question

Select all visible cells after filter with macro

I apply a filter to column C by name "Rescue" and after filter match the column A and B with respective names and send the value to column D.

Sub Compare()

ActiveSheet.Range("C1").AutoFilter Field:=3, Criteria1:="Rescue"

    Dim r As Long
    Dim m As Long
    Set ws = Worksheets("Sheet1")
   
    m = ws.Range("A").End(xlUp).Rows.SpecialCells(xlCellTypeVisible).Count
    m = ws.Range("B").Rows.SpecialCells(xlCellTypeVisible).Count
  
    For r = 2 To m
     
            If ws.Range("A" & r).Value = "GT2" And ws.Range("B" & r).Value = "0" Then
            
             ws.Range("D" & r).Value = "Exclude"
            Else:
           
            If ws.Range("A" & r).Value = "GT3" And ws.Range("B" & r).Value = "0" Then
            
             ws.Range("D" & r).Value = "Exclude"
        End If
        End If
    Next r

End Sub

Thanks for help !!

Explanation / Answer

Hi..

It is not possible to iterate through rows in filtered data because it will stop at the first non contiguous row (ie. it will not go past the first hidden row) in the range.

However, it is possible to use For Each cell in a range with hidden rows so the code example below assigns the first column of visible cells to a range variable and then uses the For Each loop and then uses Offset to address the remaining columns in the range.

I have assumed that Sheet1 is the ActiveSheet. I hope this assumption is correct.

Sub Compare()
   
    Dim ws As Worksheet
    Dim rngColA As Range
    Dim rngCel As Range
    
    Set ws = Worksheets("Sheet1")
    
    ws.Range("C1").AutoFilter Field:=3, Criteria1:="Rescue"
    
    With ws.AutoFilter.Range
        Set rngColA = .Columns(1) _
                        .Offset(1, 0) _
                        .Resize(.Rows.Count - 1, 1) _
                        .SpecialCells(xlCellTypeVisible)
        
        For Each rngCel In rngColA
            If rngCel.Value = "GT2" And rngCel.Offset(0, 1).Value = "0" Then
                rngCel.Offset(0, 3) = "Exclude"
            Else
                If rngCel.Value = "GT3" And rngCel.Offset(0, 1) = "0" Then
                    rngCel.Offset(0, 3).Value = "Exclude"
                End If
            End If
        Next rngCel
    End With

End Sub

Explanation of the following which is included in the code above: (Note that the space and underscore at the end of a line is a line break in an otherwise single line of code.)

    With ws.AutoFilter.Range
        Set rngColA = .Columns(1) _
                        .Offset(1, 0) _
                        .Resize(.Rows.Count - 1, 1) _
                        .SpecialCells(xlCellTypeVisible).

Following added with Edit after initial posting::

The If condition for the 2 different conditions can be combined as below with an Or statement contained in parenthesis. (Note that the space and underscore is a line break in an otherwise single line of code.)

            If (rngCel.Value = "GT2" Or rngCel.Value = "GT3") And _
                    rngCel.Offset(0, 1).Value = "0" Then
                    
                rngCel.Offset(0, 3) = "Exclude"
                
            End If

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.