How to delete row based on cell value?

[Routus]

Hi all
I'm about to record a macro, which although lengthy is mostly straight forward, however there is one element I will have to add in some manual code, and I need advice please.

Column AA in my worksheet has a mix of cells, and I need to identify the non-numeric cells and delete the full row that holds those cells. e.g. cell AA6 holds a non-numeric value, so I need to delete row 6 completely.

Any help appreciated

Thank you

 

[Peter_SSs]

About how many rows of data altogether in your worksheet?
Hundreds of thousands?
Tens of thousands?
Thousands?
Hundreds?
Less?

 

[Routus]

About how many rows of data altogether in your worksheet?
Hundreds of thousands?
Tens of thousands?
Thousands?
Hundreds?
Less?

Hi Peter

There will eventually be around 200,000, but at the moment I am building my method using just a few hundred rows

 

[Peter_SSs]

There will eventually be around 200,000,

Some methods that will work quite well with a few hundred rows will completely bog down with 200,000.

Assuming a header row and no dates or blank cells in column AA try this with a copy of your data.
If my assumptions are incorrect, please give more details, or better still, some sample data with XL2BB that shows what variety that might be in col AA

Sub Del_Non_Numeric()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
  a = Range("AA2", Range("AA" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Not IsNumeric(a(i, 1)) Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A2").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[Routus]

Hi Peter

There will eventually be around 200,000, but at the moment I am building my method using just a few hundred rows

I have managed to sort this out now.
Thank you

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.