asked on

Not Isnumeric Delete VBA

Hi,
I need help to format this report. I need a VBA procedure which will be finding out which consequtive cells of ColA is Not IsNumeric and will delete the entire row of the First record.

I have a sample attached as "Data" and I need the output in "Report" (as attached).

I am using Excel 2003.
EE.xls

Saurabh Singh Teotia

There you use this code and it will do what you are looking for.
Saurabh..

Sub getdata()
    Dim lrow As Long, x As Long
    Dim y As Long
    lrow = 1

    Do Until lrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row

        x = InStr(Cells(lrow, "a").Value, " ") - 1
        y = InStr(Cells(lrow + 1, "a").Value, " ") - 1

        If lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Then Exit Sub
        If IsNumeric(Left(Cells(lrow, "a").Value, x)) = False And IsNumeric(Left(Cells(lrow + 1, "a").Value, y)) = False Then
            Rows(lrow).Delete
        Else
            lrow = lrow + 1
        End If
    Loop


End Sub

Open in new window

suvmitra

ASKER

Invalid procedure call or argument:
If IsNumeric(Left(Cells(lrow, "a").Value, x)) = False And IsNumeric(Left(Cells(lrow + 1, "a").Value, y)) = False Then

suvmitra

ASKER

This

suvmitra

ASKER

This Procedure is working fine with the Sample sheet. But it fails for some reason with this data (as attached).
EE.xls

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

suvmitra

ASKER

Excellent help Saurabh ..Have a nice day ahead :)

suvmitra

ASKER

Hi,
I need two exception in this rule. The procedure should ignore the line in the below cases.
I need to keep line items if col A contains:
Wire or values like AS-8000225
# 1 Exception: UCase(.Cells(x, "A").Value) Like "WIRE"

# 2 Exception: Not IsNumeric(Left(.Cells(x, "A").Value, 2)) And IsNumeric(Right(.Cells(x, "A").Value, 6))

Please help.