Not Isnumeric Delete VBA

suvmitra
suvmitra used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
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

Author

Commented:
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

Author

Commented:
This
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Author

Commented:
This Procedure is working fine with the Sample sheet. But it fails for some reason with this data (as attached).
EE.xls
Top Expert 2015
Commented:
There you go small modification, Use this one.
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 x < 0 Then x = Len(Cells(lrow, "A").Value)
 If y < 0 Then y = Len(Cells(lrow, "A").Value)
        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

Author

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

Author

Commented:

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial