VBA - How to select range of cells based on value of 2 cells in each row

[crev64]

Hello

I am trying to make a sheet to help me organize data I have gathered.

I want to write a VBA script to select every row in a range A2:E1000 where the cell in column B = "GP20" and cell in column E does not equal "Finished"

I have messed around with a few scripts I found in other posts in the forum with no luck. Can anyone help?

Thanks

[SamT]

What are you planning on doing after you select all those Rows?

Guessing that you plan to hide all the others

'Start Sub
dim Cel As Range

For each Cel in Range(Range("B2"),Cells(Rows.Count, "B").End(xlUp))
   If Cel <> "GP20" Then
      Cel.EntireRow.Hidden = True
      Exit If
   ElseIf LCase(Cel.Offset(0, 3)) = "finished" then
      Cel.EntireRow.Hidden = True
   End If
Next Cel
End Sub

If that's what you want, I can show you how to do the same thing with "G19, "G21", etc, and Finished or unfinished or any other such term.

[crev64]

Hey Sam,

I want to copy all rows that meet the criteria and paste on a new sheet. Can you help with that?

Crev64

[Paul_Hossler]

Starting with the macro recorder and making the resulting macro a little more general purpose

Option Explicit

Sub Macro1()
    Dim wsSrc As Worksheet, wsDest As Worksheet
    Dim rSrc As Range

    Set wsSrc = Worksheets("Sheet1")
    Set wsDest = Worksheets("Sheet2")
    Set rSrc = wsSrc.Cells(1, 1).CurrentRegion
    
    If wsSrc.AutoFilterMode Then wsSrc.AutoFilterMode = False
        
    With rSrc
        .Rows(1).AutoFilter
        .AutoFilter Field:=2, Criteria1:="=GP20", Operator:=xlAnd
        .AutoFilter Field:=5, Criteria1:="<>Finished", Operator:=xlAnd
        .SpecialCells(xlCellTypeVisible).Copy wsDest.Cells(1, 1)
        Application.CutCopyMode = False
    End With
    
    wsSrc.AutoFilterMode = False
    
End Sub