PDA

View Full Version : VBA - How to select range of cells based on value of 2 cells in each row



crev64
06-08-2018, 08:15 AM
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
06-08-2018, 04:05 PM
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
06-19-2018, 05:20 AM
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
06-19-2018, 05:42 AM
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