Any idea why this won't work? I keep getting Run-time error 1004: Application-defined or object-defined error. I'm trying to delete a whole section of rows if the first cell is empty, and then if it's not empty delete all the empty rows up until there's a row that contains values. It's just to easily clean up a spreadsheet. I've attached the code.enter image description here
Private Sub checkRows()
'H or 8
If IsEmpty(Range("A827").Value) = True Then
Rows("825:925").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("827:925").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'G or 7
If IsEmpty(Range("A725").Value) = True Then
Rows("723:823").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("725:823").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'F or 6
If IsEmpty(Range("A623").Value) = True Then
Rows("621:721").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("623:721").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'E or 5
If IsEmpty(Range("A521").Value) = True Then
Rows("519:619").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("521:619").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'D or 4
If IsEmpty(Range("A419").Value) = True Then
Rows("417:517").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("419:517").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'C or 3
If IsEmpty(Range("A317").Value) = True Then
Rows("315:415").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("317:415").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'B or 2
If IsEmpty(Range("A215").Value) = True Then
Rows("213:313").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("215:313").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'A or 1
If IsEmpty(Range("A113").Value) = True Then
Rows("111:211").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("113:211").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
'RP
If IsEmpty(Range("A9").Value) = True Then
Rows("7:107").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
Rows("9:107").Select
Selection.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If
End Sub
Debug
hit that and the offending line will be highlighted.