remove all cells in column not 1-8

I have a sheet Filtered_Data with column "Zone". I don't know what column Zone might be, but it will always be present on Filtered_Data.
I need to remove all rows where value in Zone is not a number 1- 8. If it is anything else or empty, I need to remove.

Can anyone help?
Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rarandallsimmonsCommented:
Filter your data using the built-in filter tool. Use the drop down on the "Zone" column to deselect the values 1-8. Then just highlight and delete the remaining rows.
Euro5Author Commented:
Sorry I didn't clarify. This is part of a much larger vba code. I need it in vba code. Sorry!
Saurabh Singh TeotiaCommented:
Euro5,

You can use the following code..

Sub deleterows()
    Dim ws As Worksheet
    Dim lcol As Long, lrow As Long
    Set ws = Sheets("Filtered_Data")

    lrow = 2

    lcol = Range("1:1").Find(What:="Zone", SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

    Do Until lrow > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        If IsNumeric(Cells(lrow, lcol).Value) = False Or Trim(Cells(lrow, lcol).Value) = "" Then
            Rows(lrow).Delete
        Else
            lrow = lrow + 1
        End If

    Loop


End Sub

Open in new window


Saurabh...
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Euro5Author Commented:
Saurabh,
The code just sits at     Do Until lrow > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
endlessly looping. Have to stop from running.
Can you help?

' Remove bad zones
Set ws = Sheets("Filtered_Data")
    lrow = 2

    lcol = Range("1:1").Find(What:="Zone", SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

    Do Until lrow > Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

        If IsNumeric(Cells(lrow, lcol).Value) = False Or Trim(Cells(lrow, lcol).Value) = "" Then
           Rows(lrow).Delete
        Else
           lrow = lrow + 1
       End If

   Loop

Open in new window

Euro5Author Commented:
Surabh,
The code is on Sub Make_Pivot()
Maybe if you try to run with data?
test-for-address.xlsb
Euro5Author Commented:
I need it to remove anything that is not a number {1,2,3,4,5,6,7,8}.
Only keep rows with these numbers in Zone column.
Saurabh Singh TeotiaCommented:
Euro5,

The code works fine on your sample data and loop comes to an end..May be your data is way too much thats the reason it's taking time...

Try running this code this will tell you which row it's working on..

Sub deleterows()
Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim lcol As Long, lrow As Long
    Set ws = Sheets("Filtered_Data")

    lrow = 2

    lcol = Range("1:1").Find(What:="Zone", SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

    Do Until lrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row
Application.StatusBar = "Working on Row Number--> " & lrow
        If IsNumeric(Cells(lrow, lcol).Value) = False Or Trim(Cells(lrow, lcol).Value) = "" Then
            Rows(lrow).Delete
        Else
            lrow = lrow + 1
        End If

    Loop

Application.StatusBar = ""
End Sub

Open in new window


Saurabh...
Euro5Author Commented:
Saurabh,
It looks like the code will only remove rows where Zone column is blank or not a number, am I right?
I need to code to remove anything that is not a number 1-8.
Would it remove a 9, for instance?
Thanks for the clarification.
Saurabh Singh TeotiaCommented:
No it won't remove 9 since its a number..do you want to remove anything which is outside of specific 1-8 like 0,10,11,12 and so on??
Euro5Author Commented:
Yes, anything that is not 2,3,4,5,6,7,8.
Sorry, before I said 1-8, but it is actually 2-8.
Saurabh Singh TeotiaCommented:
You can use this code in that case...

Sub deleterows()
Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim lcol As Long, lrow As Long
    Set ws = Sheets("Filtered_Data")

    lrow = 2

    lcol = Range("1:1").Find(What:="Zone", SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column

    Do Until lrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row
Application.StatusBar = "Working on Row Number--> " & lrow
        If (Cells(lrow, lcol).Value < 2 Or Cells(lrow, lcol).Value > 8) Or Trim(Cells(lrow, lcol).Value) = "" Then
            Rows(lrow).Delete
        Else
            lrow = lrow + 1
        End If

    Loop

Application.StatusBar = ""
End Sub

Open in new window


Saurabh...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Thank you!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.