1

i am creating some vba code which automates the removal of merged cells and then deletes the blank rows that are created as a result of the demurging. The problem comes when a tab doesnt have any blank values. When a sheet doesn't have any blank values I get an error 9. Below is the code to detect and delete blank rows from my documents:

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

should I try to include an if then statement to counteract this? Thanks in advance!

learninonthego
  • 11
  • 2
  • Actually run time error 1004, sorry! – learninonthego Jul 09 '15 at 20:51
  • If you don't care about the error/situation, you can just disable error handling temporarily. Add `On Error Resume Next` before this line and `On Error GoTo 0` after. – Bond Jul 09 '15 at 20:52
  • `SpecialCells` is one of those functions that will throw an error and is generally best handled with error handling like @Bond recommends. Depending on your data, you might be able to make use of `CountIf` to detect if there are any blank cells. I would just handle the error unless it's critical to you to avoid error handling. – Byron Wall Jul 09 '15 at 22:13

1 Answers1

0

There are a couple ways to go about handling the potential error of trying to delete something that doesn't exist.

First, you could check to see if there are blank cells.

with worksheets("Sheet1")
    with .range(.cells(1, 1), .cells(rows.count, 1).end(xlup))
        if cbool(application.countblank(.columns(1))) then
            .cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        end if
    end with
end with

'earlier version of Excel may not have COUNTBLANK
with worksheets("Sheet1")
    with .range(.cells(1, 1), .cells(rows.count, 1).end(xlup))
        if application.counta(.columns(1)) < .rows.count then
            .cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        end if
    end with
end with

The above has the disadvantage that the COUNTBLANK function will count zero-length strings returned by formula as blanks while they are not considered trly blank by the .SpecialCells(xlCellTypeBlanks) method. However, you probably wouldn't be looking for blanks in a column you know to be populated with formulas so this is a consideration, not a deal breaker.

Next we can test for Nothing by altering the error handling method.

dim delRng as range
with worksheets("Sheet1")
    with .range(.cells(1, 1), .cells(rows.count, 1).end(xlup))
        'temporarily suspend error handling
        on error resume next
        set delRng = .cells.SpecialCells(xlCellTypeBlanks)
        on error goto 0
        if not delRng  is nothing then
            delRng.EntireRow.Delete
        end if
    end with
end with

Although widely accepted, I do not favor this method simply because I don't think you should have to break something in order to see if it exists but that is just my personal preference.