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

--
Steve
-----------------------------------------------------------------------
Steve M's Profile: http://www.excelforum.com/member.php...fo&userid=3252
View this thread: http://www.excelforum.com/showthread.php?threadid=53454

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

--
mudrake
-----------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247
View this thread: http://www.excelforum.com/showthread.php?threadid=53454

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

object variable or With block variable not se

--
Steve
-----------------------------------------------------------------------
Steve M's Profile: http://www.excelforum.com/member.php...fo&userid=3252
View this thread: http://www.excelforum.com/showthread.php?threadid=53454

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.Spe cialCells(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..

--
broro18
-----------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...fo&userid=3006
View this thread: http://www.excelforum.com/showthread.php?threadid=53454

hi Steve

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



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


"Steve M" wrote in message
...

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

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" wrote
in message
...

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

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" wrote in message
...

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" wrote
in message
...

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

Thanks for the feedback Ron :-)

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


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=534540