PDA

View Full Version : Problem with deleting empty cells



tatebn
05-05-2009, 01:33 PM
I'm using a macro to delete empty cells and shift cells left.

Range(Range("A2"), Range("A2").End(xlDown)).EntireRow.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlToLeft

I have it working except that if the file in question has no blank cells it crashes. I tried putting

On Error Resume Next

afterward. Which worked for one file containing the macro but not on another.

How do I fix this?

tatebn
05-05-2009, 01:38 PM
That same macro opens a bunch of workbooks, copies their data (except the header line) and pastes it into one master file.

If it attempts to copy and paste from a file that has nothing except the header line I get an error. Basically I'm asking what is the vba equivalent of a try catch block. How do I toss these errors?

p45cal
05-05-2009, 02:45 PM
Dim xxx As Range
On Error Resume Next
Set xxx = Range(Range("A2"), Range("A2").End(xlDown)).EntireRow.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not xxx Is Nothing Then
MsgBox "there's a range"
Else
MsgBox "there's no range"
End If