[RESOLVED] How does Excel set the used range?

[doasidont]

Is there a dependable way to cause Excel to set the used range (i.e., last row, last column) of a worksheet other than saving the workbook? I have an application where I filter a sheet and then delete all visible rows. When I copy that sheet to another sheet, the used ranges of each sheet do not agree. By accident, if I msgbox the function xllastrow of each sheet, then they agree. But I don't want to msgbox and I don't want to save teh workbook. Thanks.

[vbfbryce]

What are you doing with the "used range" that requires them to agree?

[doasidont]

I am filtering Sheet1 and removing all rows that I do not need. Then I am copying Sheet1 using 'Move or Copy'. Then I want to replace all formulas on Sheet1 (2) by using 'ActiveSheet.UsedRange.Value = Sheets("Imported Data").UsedRange.Value'. Sheet1 (2) ends up with #NA rows at the end of sheet. The number of #NA rows is exactly the number of rows that I deleted on Sheet1. I've tried to active sheet calculate but that ddoesn't work. When I msgbox xlLastRow("Sheet1"), it displays the correct row number, but it is copying beyond that row. If I msgbox the Sheet1.usedrange before I copy, then the correct number of rows is copied.

[doasidont]

I got around the problem by calculating the Sheet1.usedrange before copying. Thanks.