Delete rows if value in Column "X" is out of specific range.

[powerzasty]

Hello everybody.

I am trying to do a macro which deletes all rows in specific TAB in Excel Spreadsheet if value in Column A is smaller than 13420000000 and bigger than 16000000000. First row have to stay.

I was trying to to use advance filtering and Kutools but with Kutools which kind of works but I can't record macro so the pure VBA probably will be my best bet.

I was also digging in google good while but I can't find anything specific which would work.

I am very new on macros so please be forgiving.

Please help.

[dmcgov]

can you upload a small sample workbook so that i can see the data?

[dmcgov]

try something like this:

Please Login or Register  to view this content.

[powerzasty]

It doesn't work I'm afraid Or I have edited macro incorrectly. Please see what I did:

Sub DeleteRowsBasedOnCellValue()
Dim wss As Worksheet, wst As Worksheet
Dim lRow As Long
Dim iCntr As Long
Set wss = Sheets("DP")
Set wst = Sheets("DP")

With wst
lRow = wst.Cells(wst.Rows.Count, "A").End(xlUp).Row
For iCntr = lRow To 2 Step -1
If wss.Range("A2") < 13420000000# Or wss.Range("A10000") > 16000000000# Then
.Rows(iCntr).Delete
End If
Next
End With
End Sub

This macro delete all rows. Probably minor adjustment is needed.

Please find attached Delete PN - Before.xlsx showing spreadsheet before filtration and Delete PN - After.xlsx. "After" is the spreadsheet how I would like to see after applying macro.

Please help.

[bakerman2]

How about this.

Please Login or Register  to view this content.

[powerzasty]

That works!

Unfortunately is very slow to filter (takes approximately 5 minutes) but this is probably the nature of the task given to Excel. Is there any tricky way to speed it up?

If not, I am glad having this code anyway! Thank you for that.

Please tell me if possible to make it faster, if not I will mark post as solved anyway and I leave appropriate reputation comment,

Cheers,

Powerzasty.

[bakerman2]

Maybe using Autofilter and then deleting visible rows might speed things up, but don't have time right now. Will try later today.

[bakerman2]

Try this. Had to put code in the file due to website firewall.

[powerzasty]

OMG! It looks like that simplest solutions are always the best solutions!

That one works absolutely great!

Thank you for help!

Best regards,

Powerzasty.

[bakerman2]

You're welcome and thanks for rep+.

[powerzasty]

Hello again.

Would be possible to use autoflter to select 5 different ranges?

For example to keep numbers:

12000000000 - 13000000000
13420000000 - 13460000000
14460000000 - 16000000000
23450000000 - 24460000000
34450000000 - 34460000000

and then deletet rows which are out of these ranges ?

Please advise.

Best regards,

Powerzasty.