Deleting Rows With Only Text

[jlkirk]

The cells in Column A, Rows 1-5000 contain (i) only numbers, (ii) only text, or (iii) both numbers and text. The text-only cells number over 250. What I would like is a macro that would delete all rows where only text appears in the cells in column A, leaving only rows with (i) numbers only, and (ii) numbers and text, in column A.
Thanks in advance.

 

[jlkirk]

Actually, the "numbers only" cells are treated by Excel as text. But I still want them to remain as numbers

 

[Rick Rothstein]

Give this macro a try...

Sub DeleterRowsWithTextInColumnA()
  Dim X As Long, UnusedColumn As Long, LastRow As Long
  Const StartRow As Long = 2
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = StartRow To LastRow
    If Not Cells(X, "A").Value Like "*#*" Then Cells(X, UnusedColumn).Value = "X"
  Next
  Columns(UnusedColumn).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
End Sub

I assumed your data started in Row 2 (with Row 1 containing headers)... if that is not correct, then change the StartRow constant (the Const statement) to the row number where your data actually starts at.

 

[jlkirk]

Works great! Thanks.