Deleting positive values in 45,000 row database

[pcarmour]

Hi,
I am trying to delete all rows that have a positive values in column "H".
I have tried the following which did work but now crashes excel:


Sub pca()
Dim rng As Range, Cell As Range, del As Range
Dim rngNew As Range
Dim rngDelete As Range
Dim aCell As Range
Dim lastRow As Long
Set rng = Intersect(Range("Free_Money"), ActiveSheet.UsedRange)
For Each Cell In rng
If (Cell.Value) > 0 _
Then
If del Is Nothing Then
Set del = Cell
Else: Set del = Union(del, Cell)
End If
End If
Next Cell
On Error Resume Next
del.EntireRow.Delete
End Sub


Please see attached which has 45,000 rows of data in the original file - reduced for the download, is this size the problem?

Any help as always is really appreciated.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build 7601and Excel version 14.0.6123.5001 (32 bit)

[snb]

Please use code tags !! (you have posted here 109 times ??)


sub M_snb()
with columns(8)
.autofilter 1,">0"
.specialcells(12)=""
.autofilter
.specialcells(4).entirerow.delete
end with
End Sub

[pcarmour]

Hi SNB,
Yes after 109 postings I still don't fully understand VBA, that's where self learning at 66 years old gets you, but thank you, your plain and easy code works brilliantly, Thank You.

[mancubus]

Hi SNB,
Yes after 109 postings I still don't fully understand VBA, that's where self learning at 66 years old gets you, but thank you, your plain and easy code works brilliantly, Thank You.

hi.

just click the # button on the quick reply panel.

VBA tags will be inserted. paste the code between these tags.

[ CODE ]Your code here[ /CODE ]

or write here these tags without spaces (before and after the words CODE and /CODE).

[pcarmour]

Hi Mancubus,
Good to hear from you again.
OK I can see what you all are showing me, I will load correctly next time.

[snb]

@pcarmour

Using code tags has very little to do with VBA as @Mancubus clearly pointed out.
I think most of us are self taught in VBA (but isn't that the essence of learning: you all have to do it yourself, nobody else can learn something for you). So being self taught sounds to me rather tautologically.
Please do not boast your seniority without knowing somebody else's; wait until I will be your senior....(won't be that long). ;)

[pcarmour]

Hi SNB,
Thank you for your comments.
Although your code does work it is far slower than my original code was (when it worked) over this number of rows and when other code to sort the data is added it doesn't delete all positive values. Would a variation of my original code work better?

[snb]

You might introduce application.calculation=xlcalculationmanual & application.screenupdating =False.

[mancubus]

Hi Mancubus,Good to hear from you again.OK I can see what you all are showing me, I will load correctly next time.thank you pcarmour. :beerchug:

[pcarmour]

OK, thank you for your help, all working well.