0

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

VBA Window

8
  • Thanks for the heads up. I put it in as text now. The snippet was a little older and I caught that mistake already but it still doesn't want to work.
    – S. Price
    Mar 5, 2018 at 15:00
  • Now please tell us on which line is the error thrown. Mar 5, 2018 at 15:03
  • Just an FYI: your else will delete all the rows between 827 and 925, unless you have data that fills every column in those rows, all 16,000+ columns. Mar 5, 2018 at 15:12
  • It doesn't give a line for where the error is. I just get a pop up that says the error 1004. How would I make it only delete the rows that contain nothing? Sorry I'm not too experienced with VBA
    – S. Price
    Mar 5, 2018 at 15:15
  • 1
    When you see that error there is an option for Debug hit that and the offending line will be highlighted. Mar 5, 2018 at 15:15

2 Answers 2

1

You can't use Rows() with a column reference. Just change your first part to the rows only.

If IsEmpty(Range("A827").Value) = True Then
    Rows("825:925").EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
Else
    Rows("827:925").EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
End If

Or you could have done Range("A825:A925").EntireRow.Delete ...

8
  • Changed that but still not working!
    – S. Price
    Mar 5, 2018 at 15:31
  • @S.Price - How is it not working? Do you get an error? Even with the code you added in the OP, this should work. Is the code I posted working, but just giving you different outcome than you expect? The Worksheet_SelectionChange() code is in a worksheet object, right? Not a workbook module?
    – BruceWayne
    Mar 5, 2018 at 15:41
  • It says I run out of stack space and then gives an error on the first line (IsEmpty line)
    – S. Price
    Mar 5, 2018 at 15:43
  • @S.Price - Where is this macro saved? Is it in a Worksheet Object of the sheet you want it to run on? Are there any other macros running? If this is all the code you have, you shouldn't be getting that Stack Space error. You must have some other code running, no?
    – BruceWayne
    Mar 5, 2018 at 15:46
  • No other macros running and it's in the worksheet object for the xlsm file I want it to be in. The procedure seems to be set to some default selection but that shouldn't matter if the code is what it is, right?
    – S. Price
    Mar 5, 2018 at 15:50
0

The "Target" range argument is not used and you are not using the rows method correctly. If this is a static range then I suggest using something like:

Private Sub RemoveRows()

  If IsEmpty(ActiveSheet.Range("A827").Value) = True Then ActiveSheet.Range("A827:A925").Clear

End Sub

If you intend to pass a range argument I suggest passing a string value of the range rather than a complete range object:

Private Sub RemoveRows(ByVal TargetRange As String)

If IsEmpty(ActiveSheet.Range("A827").Value) = True Then ActiveSheet.Range(TargetRange).Clear

End Sub

Then to use this simply pass the string representation of the range as an argument:

Private Sub CallRemoveRows()

RemoveRows ("A827:A895")

End Sub
2
  • I've attached the full code now to show what I thought would work, and what I'm trying to achieve. My worksheet has 9 sections of 100 rows each. They are formatted with borders. I figured if I coded something that started at the bottom section and worked it's way up, deleting what needed to be deleted, the formatting wouldn't be changed. Sometimes the sections have nothing in them so in that case I wanted it to check for the very first cell and delete the entire section if nothings there. But if it did contain something, it would delete the rows up until the last line that contains values.
    – S. Price
    Mar 8, 2018 at 15:53
  • I'm not sure if your recommendation would work for that.
    – S. Price
    Mar 8, 2018 at 15:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .