Cells.Clear

B

Ben Dummar

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?
 
G

Gord Dibben

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
Edit>Clear>All


Gord Dibben MS Excel MVP
 
B

Ben Dummar

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

Gord Dibben

See in-line below

Gord

Yes,

I am trying to use it in the following macro code:
Click to expand...
Delete this line Cells.Clear ' remove previous content
Add this line ActiveSheet.Range("B3:B65536").Clear
 
Top