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

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

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

Guessing that you plan to hide all the others

Code:

---

'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.

Hey Sam,

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

Crev64

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

Code:

---

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

---