Search code examples
excelvba

Why is the same VBA code to delete rows only working once?

I have a data set that when exported to excel comes with three sets of data separated by a couple of blank rows. I am trying to delete these blank rows to make it one continuous set of data. The code I have below will run the first line, deleting the first set of rows fine, but when it goes to the second line it gives me an error, even though it is the same exact line of code? This code should be able to dynamically tell the next set of blank rows up?

Dim ws As Worksheet
Set ws = ARDetail

Dim lastrowDS3 As Range
Dim firstrowDS3 As Range
Dim lastrowDS2 As Range

Set lastrowDS3 = Range("H1000").End(xlUp).Offset(-2, 0)
Set firstrowDS3 = lastrowDS3.End(xlUp).Offset(-1, 0)
Set lastrowDS2 = firstrowDS3.End(xlUp).Offset(0, -7)

ws.Range(firstrowDS3, lastrowDS2).Delete
ws.Range(firstrowDS3, lastrowDS2).Delete`

I get a "Run-time error'1004' method 'range' of object'_worksheet' failed"

Solution

  • You are effectively searching upward from the bottom of your sheet (assuming you have fewer than 1000 rows):

    LastrowDS3 starts in H1000, then looks upward until the last cell before a blank (let's say H502, with row 500 and 501 blank), and then offsets two upward: H500.

    FirstrowDS3 now does this again: looks upward to the next cell before a blank, perhaps H202 (with row 200 and 201 blank), then offsets one more: H201.

    Now LastrowDS2 looks up from there. I believe this finds H200; it then offsets to A200.

    Now you delete: ws.range(H201,A200).delete --> the blank cells on rows 200 and 201 are deleted (from A through H).

    Now you delete again: ws.range(H201,A200).delete.

    There are two problems. First, deleting cells can fool existing ranges (I'm not exactly sure of the rules); those cells (H201 and A200) may no longer exist until after your macro finishes and the spreadsheet adjusts to the delete. I suspect this is why you are getting 'method _range failed', because it can not find those cells.

    Second: even if the delete did work, you would be deleting cells that now have data in them.

    Put another way: your code is the same, but the environment it's operating on has changed. That's why it's not working.