Delete Row if column A contains non-numeric data or is blank

Newbie1448975

New Member
Joined
Dec 5, 2007
Messages
14
Hello all,

I am trying to create a macro that will delete the entire row if column A contains non-numeric data or is blank.
I am importing data from a different spreadsheet that has column headers or sometimes is blank in column A but contains data in other columns. So I would like to only keep the row if column A contains numeric data.

Your help is greatly appreciated.
Thanks!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
See if this helps

Code:
Dim Arr As Variant
Dim Rng As Range
Dim i as Long
Arr = Array(xlCellTypeBlanks, xlCellTypeConstants)
For i = 0 To UBound(Arr)
Set Rng = Columns("A:A").SpecialCells(Arr(i), 22)
    If Not Rng Is Nothing Then
        Rng.EntireRow.Delete
    End If
Next i

VBA Noob
 
Upvote 0
Code:
Sub DelTest()
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
End Sub
CAVEAT: There is a max ,limit of ~8100 cells to Specialcells. (just_jon MrExcel MVP)
 
Upvote 0
Or another way

Code:
Dim Rng As Range, C As Range
Dim Lr As Long
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = Range("A1:A" & Lr)

Application.ScreenUpdating = False
    For Each C In Rng
        If Not IsNumeric(C) Then
            C.EntireRow.Delete
        End If
    Next C
Application.ScreenUpdating = True

VBA Noob
 
Upvote 0
Code:
Sub DelTest()
    Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
End Sub
CAVEAT: There is a max ,limit of ~8100 cells to Specialcells. (just_jon MrExcel MVP)

That works as well. Thanks!
 
Upvote 0
I think this will do what you want
Code:
Dim deleteRange As Range
With ThisWorkbook.Sheets("sheet1").Range("A:A")
    On Error Resume Next
      Set deleteRange = .SpecialCells(xlCellTypeBlanks)
      Set deleteRange = .SpecialCells(xlCellTypeConstants, xlErrors + xlTextValues + xlLogical)
      Set deleteRange = Application.Union(deleteRange, .SpecialCells(xlCellTypeBlanks))
      deleteRange.EntireRow.Delete
    On Error GoTo 0
End With
 
Upvote 0
Hi all,

I want to delete for the column B but when i use this 2 formula i cannot delete it.

i using this 1st formula

Dim Arr As Variant
Dim Rng As Range
Dim i as Long
Arr = Array(xlCellTypeBlanks, xlCellTypeConstants)
For i = 0 To UBound(Arr)
Set Rng = Columns("B:B").SpecialCells(Arr(i), 22)
If Not Rng Is Nothing Then
Rng.EntireRow.Delete
End If
Next i

But he take a longer time to delete the data.

Then i use this 2nd formula


Dim deleteRange As Range
With ThisWorkbook.Sheets("sheet1").Range("B:B")
On Error Resume Next
Set deleteRange = .SpecialCells(xlCellTypeBlanks)
Set deleteRange = .SpecialCells(xlCellTypeConstants, xlErrors + xlTextValues + xlLogical)
Set deleteRange = Application.Union(deleteRange, .SpecialCells(xlCellTypeBlanks))
deleteRange.EntireRow.Delete
On Error GoTo 0
End With

The file cannot be clean.

Got any where to delete the non - numeric data for coloum B?
My data very large.

Thank for advance.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,643
Messages
6,120,702
Members
448,980
Latest member
CarlosWin

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