Delete cell if criteria is not met

[gtd526]

Hello,
In Column A, from A2 down(number of cells down will differ), if the first character in a cell is NOT a number, then delete cell, shifting up on delete.
Thank you.

Replace Files.xlsm
	A
1	Old File Name
2	01. Intro.mp3
3	Under And Over It.mp3
4	03. Burn It Down.mp3
5	American Capitalist.mp3
6	05. Hard To See.mp3
7	06. Coming Down.mp3
8	Bad Company.mp3
9	08. White Knuckles.mp3
10	09. Drum Solo.mp3
11	Far From Home.mp3
12	11. Never Enough.mp3
13	War Is The Answer.mp3
14	13. Remember Everything.mp3
15	14. No One Gets Left Behind.mp3
16	The Bleeding.mp3
17
Test

 

[gtd526]

Hello,
In Column A, from A2 down(number of cells down will differ), if the first character in a cell is NOT a number, then delete cell, shifting up on delete.
Thank you.

Replace Files.xlsm
	A
1	Old File Name
2	01. Intro.mp3
3	Under And Over It.mp3
4	03. Burn It Down.mp3
5	American Capitalist.mp3
6	05. Hard To See.mp3
7	06. Coming Down.mp3
8	Bad Company.mp3
9	08. White Knuckles.mp3
10	09. Drum Solo.mp3
11	Far From Home.mp3
12	11. Never Enough.mp3
13	War Is The Answer.mp3
14	13. Remember Everything.mp3
15	14. No One Gets Left Behind.mp3
16	The Bleeding.mp3
17
Test

I can loop thru the cells in a column, but how do I check the first character in cell IsNumber?
For Each c In Range("A:A")
If Left(c.Value, 1) = IsNumber Then
MsgBox "FindMe found at " & c.Address
End If
Next

thank you

 

[Peter_SSs]

If your list is not too long, try this with a copy of your data.

Sub DeleteNonNumeric()
  Dim r As Long
  
  Application.ScreenUpdating = False
  For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Not IsNumeric(Left(Range("A" & r).Text, 1)) Then Rows(r).Delete
  Next r
  Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

If your list is very large, then this will delete the rows much faster.

Sub DeleteNonNumeric_v2()
  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("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If Not IsNumeric(Left(a(i, 1), 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

 

[gtd526]

If your list is not too long, try this with a copy of your data.

Sub DeleteNonNumeric()
  Dim r As Long
 
  Application.ScreenUpdating = False
  For r = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Not IsNumeric(Left(Range("A" & r).Text, 1)) Then Rows(r).Delete
  Next r
  Application.ScreenUpdating = True
End Sub

Thank you for the reply.
It works, but it deletes the row.
Can we just delete the cell instead of the row?
Cells in the column may be up to 20 max.
Im working with what you gave to only delete the cell.
thx

 

[Peter_SSs]

Can we just delete the cell instead of the row?

If Not IsNumeric(Left(Range("A" & r).Text, 1)) Then Rows(r).Delete
If Not IsNumeric(Left(Range("A" & r).Text, 1)) Then Range("A" & r).Delete Shift:=xlUp

 

[gtd526]

If Not IsNumeric(Left(Range("A" & r).Text, 1)) Then Rows(r).Delete
If Not IsNumeric(Left(Range("A" & r).Text, 1)) Then Range("A" & r).Delete Shift:=xlUp

Perfect! Thank you very much.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.