VBA Code - Delete rows once column A no longer numbers

tlc53

Active Member
Joined
Jul 26, 2018
Messages
349
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,572
Office Version
2010
Platform
Windows
Does this macro do what you want...
Code:
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
 

Forum statistics

Threads
1,085,872
Messages
5,386,487
Members
402,001
Latest member
smherra

Some videos you may like

This Week's Hot Topics

Top