Defining Range Extent

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

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
 
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
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 said:
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
Click to expand...
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
Click to expand...
code will run faster as a result (although the savings
 
Back
Top