.UsedRange greater than expected

Question

I have a sheet where data is written to, before this can happen it first needs to be emptied.

The sheet has a header which is 3 rows thick and is on the worksheet from A1:NQY3 but may stretch as far as A1:NVU3

The method I chose to empty the sheet is:

Set ws = ThisWorkbook.Worksheets("Sheet1")
With ws
    .Range(.cells(4, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Delete
End With

The procedure above works fine and all data, formatting and data validation rules is gone. The problem occurs when I run the procedure again on an empty sheet. The procedure would then delete the third header rows as the .UsedRange.Rows.Count should evaluate to 3 and the range to delete would become .Range(.Cells(4, 1), .Cells(3, 9931)) (NQY is column 9931)

To avoid this I added a check:

Set ws = ThisWorkbook.Worksheets("Sheet1")
With ws
    If .UsedRange.Rows.Count = 3 Then Exit Sub
    .Range(.cells(4, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Delete
End With

When stepping through this on a sheet which had just been emptied by the first procedure, I checked what the .UsedRange evaluated to since the procedure stepped to the .Delete part of the procedure instead of the Exit Sub as I expected. The .UsedRange evaluated to A1:NQY6 instead of the expected A1:NQY3

To find if there was any data in this range that was not in the header range I ran:

For Each cl In .UsedRange
    If Not cl = Empty And cl.Row > 3 Then
        MsgBox "Data found in " & cl.Address & "."
    End If
Next

When I ran this, the message box did not pop up indicating to me there is no data in that range. Why is it that when I delete a range, based on the UsedRange dimensions, the used range is larger than expected?

I really wouldn't suggest using UsedRange to figure out where the end of your data is. There's plenty of posts using the FIND or End(XlUp) methods. Saying that - I think saving the workbook after you've deleted the range will reset the used range to the correct row. — Darren Bartrup-Cook
@DarrenBartrup-Cook, I agree, there are many, many routes to Rome. Normally I would use XlUp or XlDown but the data is nowhere continuous, not a single column or row is continuous which is why I ended up using UsedRange — SilentRevolution
It would be better to use the FIND method then - it doesn't rely on certain columns or rows. A few methods are show on this link: stackoverflow.com/questions/11169445/… — Darren Bartrup-Cook

Xabier Xabier · Accepted Answer · 2018-01-05T12:57:05

How about replacing your code with something such as this below, which will delete all rows below and including row 4:

Sheet1.Rows("4:" & Rows.Count).Delete

I'm not sure as to why the Used range varies, I tend not to rely on it and simply delete something explicitly, hope this helps.

.UsedRange greater than expected

1 Answers