Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook)
Dim rngActive As Range, Cell As Range
Dun rng as Range

With wbCopy.Worksheets("Project Log Form")
Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row
Set rngActive = .Range("A9:A" & rw)

Dim r As Long
For r = rw To 9 Step -1
If UCase(.Range("G" & r)) = "CLOSED" Then
if rng is nothing then
set rng = .Range("G" & r)
else
set rng = Union(rng,.Range("G" & r))
end if
End If
Next r
End With
if not rng is nothing then
rng.EntireRow.Delete
end if
End Sub

another way is to use find

Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook)
dim rng as Range
Set rng = Nothing
Do
if not rng is nothing then
rng.EntireRow.Delete
end if
set rng = wbcopy.Columns(7).Find( _
What:="Closed", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

Loop until rng is nothing
End Sub

--
Regards,
Tom Ogilvy

"Andibevan" wrote:

I am currently using the following loop to delete unwanted rows from a
specified sheet on a workbook:-

Sub RemoveUnwantedRows_Plog(wbCopy As Excel.Workbook)
Dim rngActive As Range, Cell As Range

With wbCopy.Worksheets("Project Log Form")
Dim rw As Long: rw = .Cells(Rows.count, "A").End(xlUp).Row
Set rngActive = .Range("A9:A" & rw)

Dim r As Long
For r = rw To 9 Step -1
If UCase(.Range("G" & r)) = "CLOSED" Then
.Rows(r).Delete
End If
Next r
End With
End Sub


Is this the most efficient loop that I could use? It takes about 2 minutes
to complete this loop on a spreadsheet with about 1400 rows. Admittedly
this is on quite a small laptop so would be faster on a desktop but could I
improve on my code.

TIA

Andi