I've used the code below that was suggested to delete all of the old
data from a worksheet and hopefully reset the UsedRange back to just the
height of the header row but when I check the UsedRange of the worksheet I
still get 65536 as the used rows. Hence when I try to loop through my code
it tries to test all 65536 Rows. IDEAS? Here is the code below.

I only want to test the used range of the worksheet, I can't test for a
blank row since many exist in the document. I just need the last row of
data.


First_Row = 3
Application.Worksheets("Work Sheet").Rows(First_Row).Resize(65536 -
First_Row).EntireRow.Delete

Debug.Print Application.Worksheets("Work Sheet").Rows.Count

641 is the right answer???

It is the right number of rows.

--
Regards,
Tom Ogilvy


"HotRod" wrote in message
...
641 is the right answer???

Why in the following example do I get two differnet answers. Whats the
difference between the two?


Set Rng = Worksheets("Main Sheet").UsedRange.Rows
Main_Sheet_Count = Worksheets("Main Sheet").UsedRange.Count

Debug.Print Main_Sheet_Count = 7692

Debug.Print Rng.Rows.Count = 641

Because you usedrange has multiple columns.

count is #rows x #columns

--
Regards,
Tom Ogilvy


"HotRod" wrote in message
...
Why in the following example do I get two differnet answers. Whats the
difference between the two?


Set Rng = Worksheets("Main Sheet").UsedRange.Rows
Main_Sheet_Count = Worksheets("Main Sheet").UsedRange.Count

Debug.Print Main_Sheet_Count = 7692

Debug.Print Rng.Rows.Count = 641

that alone doesn't reset the Usedrange. It is just required.

This worked for me:

Sub ResetUsed()
Debug.Print "Work sheet: " & Application. _
Worksheets("Work Sheet").UsedRange.Rows.Count

First_Row = 3
With Application.Worksheets("Work Sheet")
.Rows(First_Row + 1).Resize(65536 - _
First_Row).EntireRow.Delete
.UsedRange
End With

Debug.Print "Work sheet: " & Application. _
Worksheets("Work Sheet").UsedRange.Rows.Count

End Sub

this
? activesheet.rows.count
65536

always returns the total rows in a sheet which is always 65536 - it has
nothing to do with the used range.

--
Regards,
Tom Ogilvy



"HotRod" wrote in message
...
I've used the code below that was suggested to delete all of the old
data from a worksheet and hopefully reset the UsedRange back to just the
height of the header row but when I check the UsedRange of the worksheet I
still get 65536 as the used rows. Hence when I try to loop through my code
it tries to test all 65536 Rows. IDEAS? Here is the code below.

I only want to test the used range of the worksheet, I can't test for
a
blank row since many exist in the document. I just need the last row of
data.


First_Row = 3
Application.Worksheets("Work Sheet").Rows(First_Row).Resize(65536 -
First_Row).EntireRow.Delete

Debug.Print Application.Worksheets("Work Sheet").Rows.Count

Thanks Tom, your explanation explains a few things. GREAT