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

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

--
Regards,
Tom Ogilvy


"hon123456" wrote in message
ups.com...
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:

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?

--


Gary


"Tom Ogilvy" wrote in message
...
Sheet1.Range("A8").CurrentRegion.Clear

--
Regards,
Tom Ogilvy


"hon123456" wrote in message
ups.com...
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

this is one way

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

--


Gary


"hon123456" wrote in message
ups.com...
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

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)).ClearConten ts '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

"hon123456" wrote in message
ups.com...
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

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)

"GKeramidas" wrote in message
...
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?

--


Gary


"Tom Ogilvy" wrote in message
...
Sheet1.Range("A8").CurrentRegion.Clear

--
Regards,
Tom Ogilvy


"hon123456" wrote in message
ups.com...
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

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




--
Regards,
Tom Ogilvy



"GKeramidas" wrote in message
...
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?

--


Gary


"Tom Ogilvy" wrote in message
...
Sheet1.Range("A8").CurrentRegion.Clear

--
Regards,
Tom Ogilvy


"hon123456" wrote in message
ups.com...
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