.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, Jan 05 '18 at 13:07
@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, Jan 05 '18 at 13:41
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: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba — Darren Bartrup-Cook, Jan 05 '18 at 13:52

score 0 · Accepted Answer · answered Jan 05 '18 at 12:57

0

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.

answered Jan 05 '18 at 12:57

Xabier

7,587
1
8
20

I will try that out, on of my main obstacles in this document is scalability and performance and that is one of the reasons for using the UsedRange boundaries to get a range to delete not wanting to overkill. – SilentRevolution Jan 05 '18 at 13:46
@SilentRevolution I would imagine that by using the one line of code instead of your UsedRange, it would in effect be more efficient, and probably faster too, as the system wouldn't have to calculate the used range and it would simply delete anything below row number 4... – Xabier Jan 05 '18 at 13:56
Possibly, but doesn't deleting the entire sheet, bar the header range, require Excel to regenerate the entire worksheet? – SilentRevolution Jan 05 '18 at 13:59
@SilentRevolution, that's a good question, I doubt it regenerates the rows, just like when you manually delete a row or cell and Excel gives you the option to shift up or left, etc.. I think (not sure) that excel simply shifts empty rows to where the deleted range originally was... – Xabier Jan 05 '18 at 14:04
At risk of turning this into a discussion. True, but when you delete an entire row, the data below is shifted upwards in place of the deleted row. When this happens without somehow generating a new row, maybe at the end of the sheet, I would assume to see the size of the sheet decrease but since the size of a sheet is fixed and doesn't decrease with row deletion, I assume(d) row regeneration. – SilentRevolution Jan 05 '18 at 19:10

.UsedRange greater than expected

1 Answers1