Delete rows if cell less than 4

[Bendo]

Hi all,
newbie at vba...
I'm trying to figure out how to look down a column of numbers, say column G, and delete any rows where the number is less than, say 4. This number may change in future.

Any help appreciated.
Bendo

[GTO]

Try:


Option Explicit

Sub exa()
Dim lLastRow As Long
Dim i As Long
lLastRow = Cells(Rows.Count, "G").End(xlUp).Row
If lLastRow >= 2 Then
For i = lLastRow To 2 Step -1
If Range("G" & i).Value < 4 Then
Range("G" & i).EntireRow.Delete
End If
Next
End If
End Sub

Please note that I did not qualify the ranges, though they probably should be.

Hope that helps,

Mark

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "G" '<=== change to suit
Dim LastRow As Long
Dim rng As Range
With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
.Rows(1).Insert
.Cells(1, TEST_COLUMN).Value2 = "temp"
Set rng = .Cells(1, TEST_COLUMN).Resize(LastRow + 1)
rng.AutoFilter field:=1, Criteria1:=">4"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End With
End Sub

[Bendo]

Hey Mark!
Unbelievable!!!

That was too quick. I stuck it in the module and away it went.
Bloody good... worked like a charm.

One question...
what does "Option explicit" mean?
I had to comment this out. I am a learner you know :)
But thanks,
I'll be taking this apart to try to understand how it works.
Hope I can help others in future.
Bendo.

[GTO]

@Bendo:

You are most welcome.

Check out the help topics for Option Explicit/Binary/Text. Basically the Explicit option requires you to declare all variables. I would sure recommend you put it back in and "fix" any undeclared variables. Mostly because this as a habit will save you time debugging later, when you have to chase down some silly misspell.

Go to Tools|Options|Editor tab and tick the <Require variable declaration> checkbox, and Option Explicit will auto-insert ea time you create a new module of any type.

BTW, I would try Bob's suggestion also. I have a sneaky suspicion it'd be faster if a large amount of rows/data.

@xld:

I've noticed this a couple of times and meant to ask... Why the dot for Rows.Count? Since its just a number, I always left off any qualifying.

Mark

[Bendo]

Hi XLD,
sorry mate, didn't see your reply for some reason. I'll give it a go also.
Thanks for the quick reply.
Bendo.

[Bob Phillips]

I've noticed this a couple of times and meant to ask... Why the dot for Rows.Count? Since its just a number, I always left off any qualifying.


It is a number, but Rows is a property of worksheet, so as I am working within a sheet object I qualify the properties of that sheet. It is unlikely to cause a problem your way, but I code defensively :)

[GTO]

...but I code defensively :)

:thumb Certainly can never hurt; thanks again.