I have a problem when I am trying to clear all of the data off of a
worksheet except the Header rows. For example I may to clear any row after
#7 of all data, I've been using the range A7 to Z55500 EntireRow.Delete

BUT

Once I've copied the new records to that worksheet and want to loop through
them the count Range is really really high 55500+ and includes thousand of
blank rows. How can I clear a work sheet without messing up the used range?

Rows(8).Resize(65536-7).Entirerow.Delete

--
Regards,
Tom Ogilvy


"HotRod" wrote in message
...
I have a problem when I am trying to clear all of the data off of a
worksheet except the Header rows. For example I may to clear any row after
#7 of all data, I've been using the range A7 to Z55500 EntireRow.Delete

BUT

Once I've copied the new records to that worksheet and want to loop
through
them the count Range is really really high 55500+ and includes thousand of
blank rows. How can I clear a work sheet without messing up the used
range?

Tom I'll give it a try THANKS


"Tom Ogilvy" wrote in message
...
Rows(8).Resize(65536-7).Entirerow.Delete

--
Regards,
Tom Ogilvy


"HotRod" wrote in message
...
I have a problem when I am trying to clear all of the data off of a
worksheet except the Header rows. For example I may to clear any row
after
#7 of all data, I've been using the range A7 to Z55500 EntireRow.Delete

BUT

Once I've copied the new records to that worksheet and want to loop
through
them the count Range is really really high 55500+ and includes thousand
of
blank rows. How can I clear a work sheet without messing up the used
range?

Tom
I've used the code that you have suggested 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've also posted this as a new topic.


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

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

I answered your later posting of this question.

--
Regards,
Tom Ogilvy


"HotRod" wrote in message
...
Tom
I've used the code that you have suggested 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've also posted this as a new topic.


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

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