Remove Rows from Excel based on content

[hpl2001]

Hello:

I'm trying to put together a function that will delete a row if the first cell in the row is blank or contains non-numeric characters. I can't just test for the cell to be numeric, because all the cells are formatted as general. A variable number of the rows will have values like '100012122' in cell A - those are the rows I want to keep. I need to delete all the other rows.

Any thoughts?

TIA...Holly

 

[SkipVought]

hi,

What code do you have?

What code specifically do you have trouble with?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

You might also check out the IsNumeric() function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hpl2001]

I was using this:

Sub DeleteNonNumeric_ColE()
On Error Resume Next
Range("e:e").SpecialCells(xlBlanks, _
xlTextValues).EntireRow.Delete
Range("e:e").SpecialCells(xlConstants, _
xlTextValues).EntireRow.Delete
Range("e:e").SpecialCells(xlFormulas, _
xlTextValues + xlErrors + xlLogical).EntireRow.Delete
On Error GoTo 0
End Sub


...but it deleted everything

 

[hpl2001]

This code sorta works:

For i = 1 To 8000
If IsNumeric(Cells(i, 1).Value) = False Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
Exit For
Next

But it's only looking at the first row, and not deleting it if it's blank

 

[SkipVought]

give this a try. Just supply a range that correspondes to the column you want data deleted in.

Sub DeleteNonNumeric(rng As Range)
    Dim lRow As Long
    
    With rng.Parent
        For lRow = .UsedRange.Rows.Count + .UsedRange.Row - 1 To .UsedRange.Row Step -1
            With .Cells(lRow, rng.Column)
                If .Value = "" Then
                    .EntireRow.Delete xlUp
                ElseIf Not IsNumeric(.Cells(1, 1).Value) Then
                    .EntireRow.Delete xlUp
                End If
            End With
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hpl2001]

Got it. Thanks very much for your help.

H.