VBA Code - Delete rows once column A no longer numbers

[tlc53]

Hi,

I have a set of data which is exported into excel and I would like to automatically clean it up, so that it can be analysed.

Once I select the entire data and sort it by column A, smallest to largest, it is then sorted by ticket number. I would then like to delete the rubbish on the bottom, which starts when column A is no longer a number.

For example;
Cell A12366 = 745577
Cell A12367 = 745578
Cell A12368 = #Orders
Cell A12369 = AA Insurance

So it would delete row 12368 down (in this case, down to row 16465).

Can someone please help me with this VBA code?

Thank you!

 

[Rick Rothstein]

Does this macro do what you want...

Sub DeleteRubbishRows()
  Dim LastNumberRow As Long, LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastNumberRow = Evaluate(Replace("MAX(IF(ISNUMBER(A1:A#),ROW(A1:A#)))", "#", LastRow))
  Range(LastNumberRow + 1 & ":" & LastRow).Clear
End Sub

 

[tlc53]

Does this macro do what you want...

Perfect! Thank you