Is there a way to change "Cells.Clear" so
that it only clears a certain section instead of the whole sheet? We used
abbreviations for our sheet names but would like to have the longer name next
to the abbreviated hyperlink for people new to the abreviations.

I have modified it so that the hyperlinks appera in Column B, starting in
cell 3, can we have it clear only column B from 3+ instead of the whole sheet?

Ben

I would just use a range.

ActiveSheet.Range("B3:B65536").Clear

Do you need this in a macro?

Might be just as eay to select B3 then SHIFT + End + DownArrow and
EditClearAll


Gord Dibben MS Excel MVP

On Wed, 24 Jan 2007 13:55:01 -0800, Ben Dummar
wrote:

Is there a way to change "Cells.Clear" so
that it only clears a certain section instead of the whole sheet? We used
abbreviations for our sheet names but would like to have the longer name next
to the abbreviated hyperlink for people new to the abreviations.

I have modified it so that the hyperlinks appera in Column B, starting in
cell 3, can we have it clear only column B from 3+ instead of the whole sheet?

Yes,

I am trying to use it in the following macro code:

---
Private Sub Worksheet_Activate()
Dim anySheet As Worksheet
Dim myCounter As Long

Cells.Clear ' remove previous content
myCounter = 0
For Each anySheet In Worksheets
If anySheet.Name < ActiveSheet.Name Then
Range("B2").Offset(myCounter, 0) = anySheet.Name
'this adds hyperlink remove if you don't want it
ActiveSheet.Hyperlinks.Add anchor:=Range("B2").Offset(myCounter, 0), _
Address:="", SubAddress:="'" & anySheet.Name & "'!A1"
'update the offset counter, keep this
myCounter = myCounter + 1
End If
Next
End Sub
----

"Gord Dibben" wrote:

Ben

I would just use a range.

ActiveSheet.Range("B3:B65536").Clear

Do you need this in a macro?

Might be just as eay to select B3 then SHIFT + End + DownArrow and
EditClearAll


Gord Dibben MS Excel MVP

On Wed, 24 Jan 2007 13:55:01 -0800, Ben Dummar
wrote:

Is there a way to change "Cells.Clear" so
that it only clears a certain section instead of the whole sheet? We used
abbreviations for our sheet names but would like to have the longer name next
to the abbreviated hyperlink for people new to the abreviations.

I have modified it so that the hyperlinks appera in Column B, starting in
cell 3, can we have it clear only column B from 3+ instead of the whole sheet?

See in-line below

Gord

On Wed, 24 Jan 2007 14:31:01 -0800, Ben Dummar
wrote:

Yes,

I am trying to use it in the following macro code:

---
Private Sub Worksheet_Activate()
Dim anySheet As Worksheet
Dim myCounter As Long

Delete this line Cells.Clear ' remove previous content
Add this line ActiveSheet.Range("B3:B65536").Clear

myCounter = 0
For Each anySheet In Worksheets
If anySheet.Name < ActiveSheet.Name Then
Range("B2").Offset(myCounter, 0) = anySheet.Name
'this adds hyperlink remove if you don't want it
ActiveSheet.Hyperlinks.Add anchor:=Range("B2").Offset(myCounter, 0), _
Address:="", SubAddress:="'" & anySheet.Name & "'!A1"
'update the offset counter, keep this
myCounter = myCounter + 1
End If
Next
End Sub
----

"Gord Dibben" wrote:

Ben

I would just use a range.

ActiveSheet.Range("B3:B65536").Clear

Do you need this in a macro?

Might be just as eay to select B3 then SHIFT + End + DownArrow and
EditClearAll


Gord Dibben MS Excel MVP

On Wed, 24 Jan 2007 13:55:01 -0800, Ben Dummar
wrote:

Is there a way to change "Cells.Clear" so
that it only clears a certain section instead of the whole sheet? We used
abbreviations for our sheet names but would like to have the longer name next
to the abbreviated hyperlink for people new to the abreviations.

I have modified it so that the hyperlinks appera in Column B, starting in
cell 3, can we have it clear only column B from 3+ instead of the whole sheet?