3

I have been trying to use some snippets on how to delete entire rows on Excel VBA, but I can't modify them to include the "IsNumber" verification.

I need to be able to choose an active area, like:

Set r = ActiveSheet.Range("A1:C10")

And as it goes through row after row (and checking every cell of the area), delete the entire row if a there is a number on a cell.

For example:

NA NA NA 21
NA 22 NA 44
00 NA NA NA
NA NA NA NA
55 NA NA NA

The macro would then delete all the rows, except for the 4th one which is 

NA NA NA NA
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
tracer
  • 63
  • 1
  • 6
  • Range("B1:C5").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete – tracer Aug 01 '12 at 21:25
  • Thanks for the suggestions and help, everyone. The comment just above this one does exactly what I want, and I hope it can help someone. – tracer Aug 01 '12 at 21:36
  • 1
    That is an incorrect way to use it and it can throw an error at any point of time. I gave you 2 tried and tested methods and you mean that both didn't help? – Siddharth Rout Aug 01 '12 at 21:45

3 Answers3

5

Take your pick :)

WAY 1 (TRIED AND TESTED)

This uses SpecialCells to identify the rows which has numbers.

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    On Error GoTo Whoa

    Set ws = Sheets("Sheet1")

    With ws
        Set rng = .Cells.SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow

        rng.ClearContents '<~~ or rng.Clear if cells have formatting

        .Cells.Sort Key1:=.Range("A1")
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

WAY 2 (TRIED AND TESTED)

This uses Looping and Count() to check for numbers

Sub Sample()
    Dim ws As Worksheet
    Dim delrange As Range
    Dim lRow As Long, i As Long

    On Error GoTo Whoa

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 1 To lRow
            If Application.WorksheetFunction.Count(.Rows(i)) > 0 Then
                If delrange Is Nothing Then
                    Set delrange = .Rows(i)
                Else
                    Set delrange = Union(delrange, .Rows(i))
                End If
            End If
        Next i

        If Not delrange Is Nothing Then delrange.Delete
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Way 3 (TRIED AND TESTED)

This uses Auto Filters. I am assuming that row 1 has headers and there is no blank cell in your range.

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long, i As Long
    Dim ColN As String

    On Error GoTo Whoa

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        For i = 1 To lCol
            '~~> Remove any filters
            .AutoFilterMode = False
            ColN = Split(.Cells(, i).Address, "$")(1)

            '~~> Filter, offset(to exclude headers) and delete visible rows
            With .Range(ColN & "1:" & ColN & lRow)

                .AutoFilter Field:=1, Criteria1:=">=" & _
                Application.WorksheetFunction.Min(ws.Columns(i)), _
                Operator:=xlOr, Criteria2:="<=" & _
                Application.WorksheetFunction.Max(ws.Columns(i))

                .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End With

            '~~> Remove any filters
            .AutoFilterMode = False
        Next
    End With

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Thanks! It did delete the content of the right cells, however, I want to delete the entire row if cell is numeric. I tried with "rng.Delete", but it doesn't seem to be like that.. – tracer Aug 01 '12 at 21:09
  • it deletes the contents of the rows and then pushes the blank rows at the bottom. You can also use `rng.Clear` instead of `rng.ClearContents` if the rows have any formatting. – Siddharth Rout Aug 01 '12 at 21:10
  • Thanks for the help! I have made a very small version with that specialCells part. I have added a comment to my original post – tracer Aug 01 '12 at 21:23
  • You cannot use Delete because of overlapping ranges. I have also updated my code above with Way 2. – Siddharth Rout Aug 01 '12 at 21:26
1
Sub DeleteNumeric()

    Dim i As Long
    Dim rCell As Range
    Dim rRow As Range
    Dim rRng As Range

    'identify the range to search
    Set rRng = Sheet1.Range("A1:D5")

    'loop backwards when deleting rows
    For i = rRng.Rows.Count To 1 Step -1
        'loop through all the cells in the row
        For Each rCell In rRng.Rows(i).Cells
            If IsNumeric(rCell.Value) Then
                'delete the row and go to the next one
                rCell.EntireRow.Delete
                Exit For
            End If
        Next rCell
    Next i

End Sub
Dick Kusleika
  • 32,673
  • 4
  • 52
  • 73
0
Dim currentPos As Integer

currentPos = 1

Do While (currentPos < yourNumberofRow)
If (Range("A" & currentPos).Value.IsNumeric = True) Then
    Rows(currentPos & ":" & currentPos).Select
    Selection.Delete
End If

currentPos = currentPos +1
Loop

Not try but easy code to understand delete and IsNumeric test.

Dekx
  • 402
  • 2
  • 15