asked on

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?

rarandallsimmons

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.

Euro5

ASKER

Sorry I didn't clarify. This is part of a much larger vba code. I need it in vba code. Sorry!

Saurabh Singh Teotia

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...

Euro5

ASKER

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

Euro5

ASKER

Surabh,
The code is on Sub Make_Pivot()
Maybe if you try to run with data?
test-for-address.xlsb

Euro5

ASKER

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 Teotia

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...

Euro5

ASKER

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 Teotia

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??

Euro5

ASKER

Yes, anything that is not 2,3,4,5,6,7,8.
Sorry, before I said 1-8, but it is actually 2-8.

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Euro5

ASKER

Thank you!!