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 to 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 appreciated

[mudraker]

Steve

Try this (make sure you have a backup copy of your data in case 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 Sub

[Steve M]

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

object variable or With block variable not set

[broro183]

Hi Steve,

Here's another option - I don't know which would be quicker, Mudraker's 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 header row):

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

This may cause an error if there are no cells less than three, if this 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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve M" <[email protected]> wrote in message
news:[email protected]...
>
> I get the following error message when I try to run it
>
> object variable or With block variable not set
>
>
> --
> Steve M
> ------------------------------------------------------------------------
> Steve M's Profile: http://www.excelforum.com/member.php...o&userid=32520
> View this thread: http://www.excelforum.com/showthread...hreadid=534540
>

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

hi Steve

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Steve M" <[email protected]> wrote in message
news:[email protected]...
>
> I get the following error message when I try to run it
>
> object variable or With block variable not set
>
>
> --
> Steve M
> ------------------------------------------------------------------------
> Steve M's Profile: http://www.excelforum.com/member.php...o&userid=32520
> View this thread: http://www.excelforum.com/showthread...hreadid=534540
>

[Ron de Bruin]

Depend what you want to do

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"broro183" <[email protected]> wrote in message
news:[email protected]...
>
> 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 Wrote:
>> hi Steve
>>
>> Look here for a few ways
>> http://www.rondebruin.nl/delete.htm
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Steve M" <[email protected]> wrote
>> in message
>> news:[email protected]...
>> >
>> > I get the following error message when I try to run it
>> >
>> > object variable or With block variable not set
>> >
>> >
>> > --
>> > Steve M
>> >
>> ------------------------------------------------------------------------
>> > Steve M's Profile:
>> http://www.excelforum.com/member.php...o&userid=32520
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=534540
>> >
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=534540
>

[broro183]

Thanks for the feedback Ron :-)

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