PDA

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