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
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
Try:
[vba]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[/vba]
Please note that I did not qualify the ranges, though they probably should be.
Hope that helps,
Mark
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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.
@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
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.
It is a number, but Rows is a property of worksheet, so as I am working withing a sheet object I qualify the propeties of that sheet. It is unlikely to cause a problem your way, but I code defensivelyOriginally Posted by GTO
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Certainly can never hurt; thanks again.Originally Posted by xld