Delete cell if criteria is not met

gtd526

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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
8Bad Company.mp3
908. White Knuckles.mp3
1009. Drum Solo.mp3
11Far From Home.mp3
1211. Never Enough.mp3
13War Is The Answer.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
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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
8Bad Company.mp3
908. White Knuckles.mp3
1009. Drum Solo.mp3
11Far From Home.mp3
1211. Never Enough.mp3
13War Is The Answer.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
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
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
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2013
Messages
338
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
May 28, 2005
Messages
48,537
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the confirmation. :)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top