I am using a single worksheet to load a record set of variable number of
rows. Before loading I clear the worksheet using.......

Columns("A:N").Select
Selection.ClearContents

I then load the data and select the set by using......

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:="DATA", RefersTo:="=audit!" &
Selection.Address()

My problem is that if the previously loaded record set used more rows than
the new record set the range selection reflects the previously loaded data
(more rows than I need). I have heard discussion about 'pure' worksheet,
where no data has been placed in cells. How do I clear the worksheet out to
create this 'pure' state or do I need to use a different command to select
the range of the new data ?

Cheers
Nigel




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

Nigel,

If you want to remove all the data in the worksheet, but retain formatting, use:

Rows.ClearContents

Rows returns a reference to all the rows in the active worksheet.

If you want to remove all information, including formats, use:

Rows.Delete

If there is data underneath the imported records but there is at east one blank row between the two areas, you can use the following
to clear ust the imported data:

Range("A1").CurrentRegion.ClearContents

CurrentRegion returns the table containing the referenced cell (A1 in this case).

You can use the same property to name your imported data:

Range("A1").CurrentRegion.Name = "audit!Data"

Notice that it is seldom necessary to select anything in VBA code and the code will run faster as a result (although the savings
here will be negligible because you are not doing much selecting)

--

John Green - Excel MVP
Sydney
Australia


"Nigel" wrote in message ...
I am using a single worksheet to load a record set of variable number of
rows. Before loading I clear the worksheet using.......

Columns("A:N").Select
Selection.ClearContents

I then load the data and select the set by using......

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:="DATA", RefersTo:="=audit!" &
Selection.Address()

My problem is that if the previously loaded record set used more rows than
the new record set the range selection reflects the previously loaded data
(more rows than I need). I have heard discussion about 'pure' worksheet,
where no data has been placed in cells. How do I clear the worksheet out to
create this 'pure' state or do I need to use a different command to select
the range of the new data ?

Cheers
Nigel




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

John
Thanks very much for the advice, I have applied the changes and all is OK

Actually there were some other parts of my code that used selects before
applying changes so I'll change these as well. This happens as I tend to
use the macro recorder to setup a starter script that I then add to / edit.
Up until now I have not change the 'selects stuff' as it didn't appear to be
a problem.

G'Day from the UK !
Cheers
Nigel

"John Green" wrote in message
...
Nigel,

If you want to remove all the data in the worksheet, but retain
formatting, use:

Rows.ClearContents

Rows returns a reference to all the rows in the active worksheet.

If you want to remove all information, including formats, use:

Rows.Delete

If there is data underneath the imported records but there is at east one
blank row between the two areas, you can use the following
to clear ust the imported data:

Range("A1").CurrentRegion.ClearContents

CurrentRegion returns the table containing the referenced cell (A1 in this
case).

You can use the same property to name your imported data:

Range("A1").CurrentRegion.Name = "audit!Data"

Notice that it is seldom necessary to select anything in VBA code and the
code will run faster as a result (although the savings
here will be negligible because you are not doing much selecting)

--

John Green - Excel MVP
Sydney
Australia


"Nigel" wrote in message
...
I am using a single worksheet to load a record set of variable number of
rows. Before loading I clear the worksheet using.......

Columns("A:N").Select
Selection.ClearContents

I then load the data and select the set by using......

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:="DATA", RefersTo:="=audit!" &
Selection.Address()

My problem is that if the previously loaded record set used more rows
than
the new record set the range selection reflects the previously loaded
data
(more rows than I need). I have heard discussion about 'pure'
worksheet,
where no data has been placed in cells. How do I clear the worksheet
out to
create this 'pure' state or do I need to use a different command to
select
the range of the new data ?

Cheers
Nigel




----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet
News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000
Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via
Encryption =---






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---