Used Rows Range?

[HotRod]

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

[HotRod]

641 is the right answer???

[HotRod]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Tom Ogilvy]

Because you usedrange has multiple columns.

count is #rows x #columns

--
Regards,
Tom Ogilvy


"HotRod" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Tom Ogilvy]

It is the right number of rows.

--
Regards,
Tom Ogilvy


"HotRod" <[email protected]> wrote in message
news:eBc%[email protected]...
> 641 is the right answer???
>
>

[HotRod]

Thanks Tom, your explanation explains a few things. GREAT