Delete Cells with only 1 character/number

Status
Not open for further replies.

SpamSerious

New Member
Joined
Aug 25, 2005
Messages
1
Welcome!

Thank you for taking the time to stop by!

I have an Excel spreadsheet that I would like to delete all the cells that have only 1 letter/number. I haven't the slightest idea of how to go about completing this, and would appreciate all the help I can acquire.

Thank you for your time and I look forward to hearing your responses!

SpamSerious
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Welcome to MrExcel Board!

How about this (in a standard code module):

Code:
Sub deleteSingles()
Dim cell As Range
    
    On Error Resume Next
    For Each cell In Cells.SpecialCells(xlCellTypeConstants)
        If Len(cell.Value) = 1 Then cell.Delete
    Next cell

End Sub

  • How to use the above code:

    Press Alt-F11 to open the VBE.
    Press Control-R to open the Project Explorer.
    Click "Microsoft Excel Objects" for the file you're working on.
    Select Insert, Module from the drop down menus.

    Open the Code pane with F7.
    Paste the above code in.
    Press Alt-Q to close the VBE and return to Excel

Hope that helps!
 
Upvote 0
Welcome to MrExcel Board!

How about this (in a standard code module):

Code:
Sub deleteSingles()
Dim cell As Range
   
    On Error Resume Next
    For Each cell In Cells.SpecialCells(xlCellTypeConstants)
        If Len(cell.Value) = 1 Then cell.Delete
    Next cell

End Sub

  • How to use the above code:

    Press Alt-F11 to open the VBE.
    Press Control-R to open the Project Explorer.
    Click "Microsoft Excel Objects" for the file you're working on.
    Select Insert, Module from the drop down menus.

    Open the Code pane with F7.
    Paste the above code in.
    Press Alt-Q to close the VBE and return to Excel

Hope that helps!
Welcome to MrExcel Board!

How about this (in a standard code module):

Code:
Sub deleteSingles()
Dim cell As Range
   
    On Error Resume Next
    For Each cell In Cells.SpecialCells(xlCellTypeConstants)
        If Len(cell.Value) = 1 Then cell.Delete
    Next cell

End Sub

  • How to use the above code:

    Press Alt-F11 to open the VBE.
    Press Control-R to open the Project Explorer.
    Click "Microsoft Excel Objects" for the file you're working on.
    Select Insert, Module from the drop down menus.

    Open the Code pane with F7.
    Paste the above code in.
    Press Alt-Q to close the VBE and return to Excel

Hope that helps!
Hi Tazguy37! Your solution is almost good for me! I want to (not delete but clear contents) of cells containg a single character, and be able to set a specific range to be operated on. Can you help?
Thanks Andrew (Zembu)
 
Upvote 0
Change the range (in red) to suit your needs.
Rich (BB code):
Sub deleteSingles()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("A1:A100")
        If Len(rng.Value) = 1 Then rng.ClearContents
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,226,588
Messages
6,191,889
Members
453,684
Latest member
Gretchenhines

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