Macro to Delete Row based on <3

[Steve M]

I have a spread sheet with data in A:A to G:G

I want a macro that will check the value in G and if its less than 3 t
delete that entire row and then move on to the next row and do the same

So if G5 is 2 it deletes A5:G5 then moves onto the next row

Any help appreciate

 

[mudraker]

Steve

Try this (make sure you have a backup copy of your data in cas
something goes wrong)

Sub DeleteRows()
Dim lRow As Long
Dim l4Row As Long

'find last used row
lRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
'loop from last used row to 1st row
'deleting rows as required
For l4Row = 1 To lRow Step -1
If Cells(l4Row, "g").Value < 3 Then
Rows(l4Row).Delete
End If
Next l4Row
End Su

 

[Steve M]

I get the following error message when I try to run it

object variable or With block variable not se

 

[broro183]

Hi Steve,

Here's another option - I don't know which would be quicker, Mudraker'
looping or the filtering below, give it a test & let us know.
I've assumed row two is the first row of data (ie row one is a heade
row):

Sub filterandDelete()
Application.ScreenUpdating = False
ActiveSheet.UsedRange.AutoFilter Field:=7, Criteria1:="<3"
Range("a2"
ActiveCell.SpecialCells(xlLastCell)).EntireRow.SpecialCells(xlCellTypeVisible).Delet
Shift:=xlUp
ActiveSheet.ShowAllData
Application.Goto Reference:="R1C1", Scroll:=True ' to return focus t
top left
Application.ScreenUpdating = True
End Sub

This may cause an error if there are no cells less than three, if thi
is possible, let me know & an error trap can be built in.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[Ron de Bruin]

hi Steve

Look here for a few ways
http://www.rondebruin.nl/delete.htm

 

[mudraker]

Steve

sorry I reversed a couple of items int he for command it should have
been'

Sub DeleteRows()
Dim lRow As Long
Dim l4Row As Long

'find last used row
lRow = Cells.Find(what:="*", searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row
'loop from last used row to 1st row
'deleting rows as required
For l4Row = lRow To 1 Step -1
If Cells(l4Row, "g").Value < 3 Then
Rows(l4Row).Delete
End If
Next l4Row
End Sub

 

[broro183]

Hi Ron,

With the variety of methods on your linked pages there must be one or
two ways that are better (eg faster, have less impact on memory etc)
than the others. Which one would you recommend?

Curious Rob

Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Ron de Bruin]

Depend what you want to do

Looping give you more control but is slower
If possible use the Filter option first

 

[broro183]

Thanks for the feedback Ron

Rob Brockett
NZ
Always learning & the best way to learn is to experience...