# Delete cell if criteria is not met

#### gtd526

##### Active Member
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
1Old File Name
201. Intro.mp3
3Under And Over It.mp3
403. Burn It Down.mp3
5American Capitalist.mp3
605. Hard To See.mp3
706. Coming Down.mp3
908. White Knuckles.mp3
1009. Drum Solo.mp3
11Far From Home.mp3
1211. Never Enough.mp3
1413. Remember Everything.mp3
1514. No One Gets Left Behind.mp3
16The Bleeding.mp3
17
Test

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### gtd526

##### Active Member
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
1Old File Name
201. Intro.mp3
3Under And Over It.mp3
403. Burn It Down.mp3
5American Capitalist.mp3
605. Hard To See.mp3
706. Coming Down.mp3
908. White Knuckles.mp3
1009. Drum Solo.mp3
11Far From Home.mp3
1211. Never Enough.mp3
1413. Remember Everything.mp3
1514. No One Gets Left Behind.mp3
16The 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

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

VBA Code:
``````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

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

VBA Code:
``````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
.Resize(k).EntireRow.Delete
End With
Application.ScreenUpdating = True
End If``````

#### gtd526

##### Active Member

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

VBA Code:
``````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``````
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

##### MrExcel MVP, Moderator
Can we just delete the cell instead of the row?
Rich (BB code):
``````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

##### Active Member
Rich (BB code):
``````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

##### MrExcel MVP, Moderator
You're welcome. Thanks for the confirmation.

Replies
1
Views
100
Replies
2
Views
107
Replies
5
Views
464
Replies
2
Views
148
Replies
0
Views
122

1,129,816
Messages
5,638,503
Members
417,030
Latest member
baqer

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back