delete rows that do not start with a number

[Homegrownandy]

Im trying to format some information from a pdf. I'll paste everything into excel, delete the rows I dont need then format the text strings to be suitable for a table.




I want to keep only the lines that start with a number, all other rows can be deleted. This is the code im working on:

Sub test()
  
    Dim Ncell As Range
    Dim IsNotNumber As Boolean
     
    For Each Ncell In Rangeleft("a" & Rows.Count).End(xlUp)
        If Not IsNumeric(Ncell) Then Rows(Ncell).Delete
           
        
    Next Ncell
     
  
End Sub

I had this working to an extent i just need this to all work as one: (obviously this is deleting the lines WITH numbers. I was just testing with it.

If IsNumeric(left(Range("a" & i).Value, 1)) Then Rows(i).Delete

All help appreciated, Thanks

Andy

[JoeM]

If you want to delete the rows that do not start with a number, then change this:

If IsNumeric(left(Range("a" & i).Value, 1)) Then Rows(i).Delete

to this:

If Not IsNumeric(left(Range("a" & i).Value, 1)) Then Rows(i).Delete

[Homegrownandy]

Hi Joe, I said i was doing this for testing, just want both to work together to be honest.

[JoeM]

I don't understand your issue/request then.

[Homegrownandy]

the first code checks the cell if its numeric and deletes as appropriate. But i only want to check the first character in that cell.

the second one does this but i cant get it working within the code I have.

I just want it to check the first character of all cells in comumn A. If its blank or begins with a letter then delete it.

Sorry for any confusion.

[Homegrownandy]

The second line of code was very temprimental becuase of the way it was written, It would only delete some then i would have to press it again to continue. Also had a problem where the "selection" would stay as the previous range and delete regardless of content of the cell.

The first box of code would deal with it better i just need to define the "left" with "Ncell" to get them to work together.

I dont know if im explaining any better, hopefully i am.

[JoeM]

When deleting rows in Excel, it is always best to work from the bottom up, as deleting rows shifts the range below it up.
Here is how I would do it:

Sub test()
  
    Dim lastRow As Long
    Dim myRow As Long
     
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
     
    For myRow = lastRow To 1 Step -1
        If Not IsNumeric(Left(Cells(myRow, 1), 1)) Then Rows(myRow).Delete
    Next myRow
       
End Sub

[Homegrownandy]

This is why you are a VIP. Thanks a lot.

[JoeM]

You are welcome!
I was actually an Excel programmer before getting into Access, so I got quite a background in it!

[Homegrownandy]

Im just proficient in persistance haha.