Delete row if cell value is < 25000000

[ddwestenskow]

Hi All,

I am trying to delete all rows where Column AB contains a number smaller than 25,000,000. While there are lots of threads on the subject I have not been able to find anything that works.

I have manipulated some code that I got from another post

http://www.mrexcel.com/forum/showthread.php?t=191042&highlight=delete+row+based+cell

I am sure that the problem has something to do with the formating of the < 25000000 part of the code, I dont see any other reason why it would not work as written. I have formated the cells in AB as numbers using commas.

Sub DeleteRowsFromBottom()
Dim i As Long, LastRow As Long
    Application.ScreenUpdating = False
        'finds last used row in column A
        LastRow = Range("AB65536").End(xlUp).Row
        'begin loop to check each row
        For i = LastRow To 2 Step -1
 
        'if cell < 25000000 delete
        If Mid(Cells(i, "AB"), 8, 7) < 25000000 Then Rows(i).Delete
 
    Next i
Application.ScreenUpdating = True
End Sub

Thanks in advance for any help.

-Dawson

 

[VoG]

Try

Sub DeleteRowsFromBottom()
Dim i As Long, LastRow As Long
    Application.ScreenUpdating = False
        'finds last used row in column A
        LastRow = Range("AB" & Rows.Count).End(xlUp).Row
        'begin loop to check each row
        For i = LastRow To 2 Step -1
 
        'if cell < 25000000 delete
        If Cells(i, "AB").Value < 25000000 Then Rows(i).Delete
 
    Next i
Application.ScreenUpdating = True
End Sub

 

[ddwestenskow]

VoG II,

Thank you for the increadibly quick response, the changes worked like a charm.

Regards,

Dawson

 

[VoG]

You are welcome! The change that made it work was

        If Cells(i, "AB").Value < 25000000 Then Rows(i).Delete

I only changed this

        LastRow = Range("AB" & Rows.Count).End(xlUp).Row

to ensure that it would function correctly in XL 2007 where there could be more than 65,536 rows of data.