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 =---