Delete row if Column C contains a number

[guypt86]

I'm using this code to delete rows if column C contains any number on sheet9 only.
But it works fine only when I'm on the same sheet (sheet9).

When I run the code when I'm on another sheet - I get an error: 'Type mismatch' pointing at row: "For I = 1 To UBound(V)"

If someone has a better code option for that, I'll be grateful : )

Option Explicit
Sub delNumRows()
    Dim V As Variant
    Dim COL As Collection
    Dim I As Long
    Dim R As Range

V = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))

Set COL = New Collection
For I = 1 To UBound(V)
    If V(I, 1) Like "*[1-9]*" Then COL.Add I
Next I

For Each V In COL
    If R Is Nothing Then
        Set R = Cells(V, 1).EntireRow
    Else
        Set R = Union(R, Cells(V, 1).EntireRow)
    End If
Next V

R.Delete

End Sub

 

[cortexnotion]

Hi @guypt86

Your range does not specify the sheet, so your code will look to the active sheet. Go with this:

V = ThisWorkbook.Sheets("Sheets9").Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))

You could definitely write something a lot shorter using visible cells only for example if you wanted to.

 

[guypt86]

Hi @guypt86

Your range does not specify the sheet, so your code will look to the active sheet. Go with this:

V = ThisWorkbook.Sheets("Sheets9").Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))

You could definitely write something a lot shorter using visible cells only for example if you wanted to.

Thanks that make sense

But for some reason I have a different error: " Application -defined or object-defined error" when I'm not at sheet9

 

[Marc L]

Hi,​

​

another way is to just move the original VBA procedure as it is to the worksheet module …​

 

[Trebor76]

When I run the code when I'm on another sheet - I get an error: 'Type mismatch' pointing at row: "For I = 1 To UBound(V)"

I'd say there is no data in Col. C.

I have made a number of changes to your code in the following all of which I put a comment next to which should see the code now run without error on the active sheet:

Option Explicit
Sub delNumRows()
    
    Dim V As Variant
    Dim COL As Collection
    Dim I As Long
    Dim R As Range
    
    Application.ScreenUpdating = False 'Turn screen updating off for more efficient processing.
    
    V = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))
    
    If Not IsEmpty(V) Then 'Only try and delete rows if variant V has something in it.
        Set COL = New Collection
        For I = 1 To UBound(V)
            If IsError(V(I, 1)) = False Then 'Skip over any cell in error like #N/A.
                If V(I, 1) Like "*[1-9]*" Then
                    COL.Add I
                End If
            End If
        Next I
        For Each V In COL
            If R Is Nothing Then
                Set R = Cells(V, 1).EntireRow
            Else
                Set R = Union(R, Cells(V, 1).EntireRow)
            End If
        Next V
        If Not R Is Nothing Then 'Only try and delete rows if range R has something in it.
            R.Delete
        End If
    End If
    
    Application.ScreenUpdating = True 'Turn screen updating back on.

End Sub

Regards,

Robert

 

[Michael M]

I might be missing something here, but why not this in the sheet module

Sub MM1()
Dim r As Long
For r = Cells(Rows.Count, "C").End(xlUp).Row To 1 Step -1
    If IsNumeric(Cells(r, 3)) Then Rows(r).Delete
Next r
End Sub

 

[vcoolio]

Hello Guypt86,

I'm with Michael on this one but, with basically one line, you could also simplify it further:-

Sub Test()

On Error Resume Next
      Sheet9.Range("C2", Sheet9.Range("C" & Sheet9.Rows.Count).End(xlUp)).SpecialCells(2, 1).EntireRow.Delete
On Error GoTo 0

End Sub

I hope that this helps.

Cheerio,
vcoolio.