View Full Version : Solved: do - until cell equals blank
markyc
06-09-2006, 03:26 AM
Hi all
I have a large database and I want to be able to delete rows where the value in F column doesn't equal Aviation.
I don't want to use the advanced filter method
I see the method as this
select F2
if this equals "Aviation" then go down one
however if F2 <> "Avaition" then delete entirerow
move down one
and repeat the above until selection = ""
Hope this is clear
Any help will be much appriecated
CCkfm2000
06-09-2006, 03:55 AM
hi markyc,
try this code below, but make sure the the word [ Avaition ] is in capitals or it will now work.
Sub test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 1 Step -1
If UCase(Cells(r, 6).Value) <> "AVAITION" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
hope this works out for you.:think:
markyc
06-09-2006, 04:14 AM
CCkfm2000
Thanks for your prompt response, it works just as wanted, just I don't want Row 1 to be deleted as this is my header row
CCkfm2000
06-09-2006, 04:26 AM
hi again,
try this
Sub test()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lastrow As Long, r As Long
lastrow = ActiveSheet.UsedRange.Rows.Count
For r = lastrow To 2 Step -1
If UCase(Cells(r, 6).Value) <> "AVAITION" Then Rows(r).Delete
Next r
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
markyc
06-09-2006, 07:18 AM
Thanks CCkfm2000 this has worked just how I wanted.
You have saved me a lot of time and hassle
Mark
CCkfm2000
06-09-2006, 08:06 AM
no problem...:hi:
cckfm2000
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.