What is the selection syntax

[hon123456]

Dear all,

I have the following sentence which select a range, and
then I want to clear the selected area.

Sheet1.Range("A8").Select
Sheet1.Range(Selection,
ActiveCell.SpecialCells(xlLastCell)).Select

Then I want to tell excel the clear the selected area? I
write the following sentence,but it is not
work.

Sheet1.range(selection).clear

What is the right syntax to point out a selection.

Is that something like that: sheet1.selection or
sheet1.range.selection ?

Thanks

 

[Tom Ogilvy]

Sheet1.Range("A8").CurrentRegion.Clear

 

[GKeramidas]

tom:

when i have values in a1 thru a15 and use your code, it deletes everything
in column a, not just a8:a15.

is this right?

 

[GKeramidas]

this is one way

Sub test()
With Sheet1.Range("a8", ActiveCell.SpecialCells(xlLastCell))
.Clear
End With
End Sub

 

[NickHK]

For many (most) actions in Excel, it is not necessary to .Select then before
you work with them.
In fact .Select slows down your code quite substantially.
So you can do the whole operation in one line

With Sheet1
.Range("A1", .Range("A1").SpecialCells(xlLastCell)).ClearContents 'Or
..Clear
End With

NickHK
P.S. There is only one "Selection" per Excel instance, so you cannot qualify
it with Sheet1 or a workbook. Similarly, there is only a single
ActiveWorkbook
ActiveSheet
ActiveCell

 

[Bob Phillips]

Yes it is. CurrentRegion region is the range bounded by any combination of
blank rows and blank columns (or spreadsheet edges), down, up, left and
right.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Tom Ogilvy]

Sure, that is what currentregion is all about. But

In contrast, if row 7 is blank, then it doesn't.

but

set rng1 = Sheet1.Range("A8").CurrentRegion
sheet1.Range("A8",rng1(rng1.count)).Clear

or if the data isn't contiguous

set rng1 = Sheet1.UserRange
sheet1.Range("A8"),rng1(rng1.count)).Clear

or
With Worksheets("Sheet1")
.Range(.Cells(8,1),.cells(rows.count,columns.count)).EntireRow.Clear
End With