2

Hey guys I am trying to write a code that deletes rows having values that are found using a formula. The problem is every other row is a #VALUE!, which I cannot change due to the setup of the report. In the end I want to delete all rows that have #VALUE! and any row that has values that are less than .75 in Column H.

The code I tried is as shown below:

Private Sub CommandButton1_Click()
    Dim rng As Range, cell As Range, del As Range
    Set rng = Intersect(Range("H1:H2000"), ActiveSheet.UsedRange)
    For Each cell In rng
        If (cell.Value) < .75 Then
            If del Is Nothing Then
                Set del = cell
            Else: Set del = Union(del, cell)
            End If
        End If
    Next cell
    On Error Resume Next
    del.EntireRow.Delete
End Sub

Any help or tips would be appreciated.

Community
  • 1
  • 1
Mike
  • 269
  • 6
  • 10
  • 18
  • 4
    Don't use a loop. Use an autofilter. Here is an example of [using an autofilter instead of a loop](http://stackoverflow.com/a/16901714/138938). It is much more efficient. – Jon Crowell Jun 11 '13 at 13:32
  • @HeadofCatering or use my more efficient row deletion sub ;) –  Jun 11 '13 at 13:48
  • 1
    @mehow, it is somewhat more efficient, but you are still using a loop. The autofilter is a set-based operation, while the loop is comparable to iterating through the rows of a SQL resultset with a cursor. – Jon Crowell Jun 11 '13 at 13:53
  • @HeadofCatering set-based approach is **almost** always the best one to choose. yeah, almost :) ok, lets not debate here –  Jun 11 '13 at 14:02
  • 1
    my 2 cents folks, use `SpecialCells` **OR** `AutoFilter` as in [StackOverFlow: how-to-delete-multiple-rows-without-a-loop-in-excel-vba](http://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba) – Our Man in Bananas Jun 12 '13 at 10:19

3 Answers3

4

I suggest stepping backwards through the rows so that when a row is deleted you don't lose your place.

Assuming that you want to look at cells contained in column H you could do something like this:

Sub Example()
    Const H As Integer = 8
    Dim row As Long

    For row = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        On Error Resume Next
        If Cells(row, H).Value < 0.75 Then
            Rows(row).Delete
        End If
        On Error GoTo 0
    Next
End Sub
Ripster
  • 3,545
  • 2
  • 19
  • 28
2

my code is an alternative to the other answers, its much more efficient and executes faster then deleting each row separately :) give it a go 

Option Explicit

Sub DeleteEmptyRows()
    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim i&, lr&, rowsToDelete$, lookFor$, lookFor2$

    '*!!!* set the condition for row deletion
    lookFor = "#VALUE!"
    lookFor2 = "0.75"

    Set ws = ThisWorkbook.Sheets("Sheet1")
    lr = ws.Range("H" & Rows.Count).End(xlUp).Row

    ReDim arr(0)

    For i = 1 To lr
     If StrComp(CStr(ws.Range("H" & i).Text), lookFor, vbTextCompare) = 0 Or _
        CDbl(ws.Range("H" & i).Value) < CDbl(lookFor2) Then
        ReDim Preserve arr(UBound(arr) + 1)
        arr(UBound(arr) - 1) = i
     End If
    Next i

    If UBound(arr) > 0 Then
        ReDim Preserve arr(UBound(arr) - 1)
        For i = LBound(arr) To UBound(arr)
            rowsToDelete = rowsToDelete & arr(i) & ":" & arr(i) & ","
        Next i

        ws.Range(Left(rowsToDelete, Len(rowsToDelete) - 1)).Delete Shift:=xlUp
    Else
        Application.ScreenUpdating = True
        MsgBox "No more rows contain: " & lookFor & "or" & lookFor2 & ", therefore exiting"
        Exit Sub
    End If

    If Not Application.ScreenUpdating Then Application.ScreenUpdating = True
    Set ws = Nothing
End Sub
  • 1
    +1 nice and elegant, might I suggest you try: `ws.Range(Left(rowsToDelete, Len(rowsToDelete) - 1)).Delete Shift:=xlUp` to avoid using `Selection`? – Our Man in Bananas Jun 12 '13 at 10:05
  • @Philip that's what I normally do and use but I wanted the selection to stand out in this example for an easier analysis of the code and its purpose. good comment thought I should have mentioned this in my answer as well –  Jun 12 '13 at 10:08
  • another alternative as the OP is to use `SpecialCells` to return the range to be deleted in one step (same for the *value <0.75* except you'd add another formula column returning an `#NA` error which can be found by **SpecialCells**) – Our Man in Bananas Jun 12 '13 at 10:09
  • ... something like Column X: 'Formula=IF(OR(VAL(H:H)<0.75,H:H="#VALUE"),"NA()","")` then `range("X:X").SpecialCells(xlCellTypeFormulas, xlErrors).entirerow.delete shift:=xlup` – Our Man in Bananas Jun 12 '13 at 10:16
  • @Philip i think that would refer to what HeadOfCatering mentioned. Feel free to post your solution for an upvote! :) –  Jun 12 '13 at 10:16
  • 1
    just a quick note on a problem i ran into using this nice approach: rowsToDelete must not exceed 255 characters, otherwise range cannot handle it. you can work around this limitation by using union. i ended up putting a delimiter into the string each time it got to 255 characters (or a multiple of it), then i split it and put the parts together via union which worked out fine (i also did not use an array, but put the string directly together to avoid the redim). – Herbert Hubert Jun 10 '14 at 08:46
0

Try:

Private Sub CommandButton1_Click()
Dim rng As Range, cell As Range, del As Range, v As Variant
Set rng = Intersect(Range("H1:H2000"), ActiveSheet.UsedRange)
For Each cell In rng
    v = cell.Text
    If v < 0.75 Or v = "#VALUE!" Then
        If del Is Nothing Then
            Set del = cell
        Else: Set del = Union(del, cell)
        End If
    End If
Next cell
On Error Resume Next
del.EntireRow.Delete
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99