Renaming colums

S

Spencer Larkin

New Member
Joined
Apr 19, 2002
Messages
1
Can I rename the column normal names A,B,C,D etc., into words of my choice ? - or

If I have a datasheet with tons of rows, how can I name the column headings so they always stay at the top row (visible) no matter how far down I scroll on the screen ?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes

If I have a datasheet with tons of rows, how can I name the column headings so they always stay at the top row (visible) no matter how far down I scroll on the screen ?


Try:

Window|Freeze Panes

from the Menu bar.
 
Upvote 0
On 2002-04-20 13:05, Spencer Larkin wrote:
Can I rename the column normal names A,B,C,D etc., into words of my choice ? - or

If I have a datasheet with tons of rows, how can I name the column headings so they always stay at the top row (visible) no matter how far down I scroll on the screen ?
Click to expand...

See the Excel Help Index topic for "Keep row and column labels visible as you scroll".
 
Upvote 0
Can I rename the column normal names A,B,C,D etc., into words of my choice ?

No Excel does not allow rename of defaut col A B C or ROWS 1 2 3 but will change in tools options to R1C1 or R1C2 etc... But use row 1 to hane headers to A1 = Jacks Data A2 = Date

The follow advice posted above this is common and exceptable,.,,, freeze panes... sounds good as said.

_________________
If you can help a guy in trouble -
If you can sort that nagging problem -
Pease try, in home, atwork or on a message board.

Others help you!
So PLEASE help if you can - If only the once.

Thank you -

Rdgs
=======
This message was edited by Jack in the UK on 2002-04-20 16:01
 
Upvote 0
1) Tools > Options >View
Deselect Row & column headings
2) Put your new names @ top
3) Freez pane
4) VBA Editor > select sheet > properties
5) Set scroll area to B2:IV65536
setting the scroll area so that the user cannot get to the Titles

You now have your own column titles
Note: The column headings have been left in
in this example to show how it really looks
and the positions;<HTML><HEAD><STYLE TYPE="text/css"></STYLE><TITLE></TITLE></HEAD><BODY bgcolor="#9F9F9F" ><FONT FACE="arial, helvetica, sans-serif"><LEFT><TABLE bgcolor="#FFFFFF" Border=2><TR><TD Align=Right ></TD><TD Align=Center >A</TD><TD Align=Center >B</TD><TD Align=Center >C</TD><TD Align=Center >D</TD><TD Align=Center >E</TD></TR><TR><TD Align=Right >1</TD><TD Align=Center bgcolor="#DFDED0"></TD><TD Align=Center bgcolor="#DFDED0">Test1</TD><TD Align=Center bgcolor="#DFDED0">Test2</TD><TD Align=Center bgcolor="#DFDED0">Test3</TD><TD Align=Center bgcolor="#DFDED0">Test4</TD></TR><TR><TD Align=Right >2</TD><TD Align=Center bgcolor="#DFDED0">1</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >3</TD><TD Align=Center bgcolor="#DFDED0">2</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >4</TD><TD Align=Center bgcolor="#DFDED0">3</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >5</TD><TD Align=Center bgcolor="#DFDED0">4</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >6</TD><TD Align=Center bgcolor="#DFDED0">5</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >7</TD><TD Align=Center bgcolor="#DFDED0">6</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >8</TD><TD Align=Center bgcolor="#DFDED0">7</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >9</TD><TD Align=Center bgcolor="#DFDED0">8</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >10</TD><TD Align=Center bgcolor="#DFDED0">9</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >11</TD><TD Align=Center bgcolor="#DFDED0">10</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right >12</TD><TD Align=Center bgcolor="#DFDED0">11</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR><TR><TD Align=Right ></TD><TD Align=Center bgcolor="#DFDED0">12</TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD><TD Align=Center ></TD></TR></TABLE></LEFT></BODY></HTML>

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font></font></font><A HREF= "http://www.gwds.co.nz/"><font color="green">http://www.gwds.co.nz/excel_files.html - Under Constru
This message was edited by Ivan F Moala on 2002-04-20 15:33
 
Upvote 0
Add this code to the "ThisWorkbook" module!
If you select Sheet1 the code erases Excel's Row and Column Headers and replaces them with custom Headers. If you select any other Sheet, Excel's normal Headers apear.

Note: You must reference each cell in Code or Formula by Normal Cell References, even if you have the custom ones in place!
When using this code, the Sheet with the custom headers must not use or reference: Row:1 or Column: A.

The code is automatic.
Note: The "Set" Ranges should be set to the number of Row and Columns you will be needing as to do the whole sheet as in the commented out "Set" statements in the code below will make the code take a long time to relabel all those rows!

The second Sub will return "Sheet1" to normal, until another Sheet is selected and you return to Sheet1!

To see how this works just copy the code below to the "ThisWorkbook" module in a default Workbook!


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook code!
Dim c&, r&

If Sh.Name = "Sheet1" Then
ActiveWindow.DisplayHeadings = False

'Set myColRng = Sheets("Sheet1").Range("B1:IV1")
Set myColRng = Sheets("Sheet1").Range("B1:AA1")
For Each Cellc In myColRng
c = c + 1
Cellc.Value = "Column: " & c
Next Cellc

'Set myRowRng = Sheets("Sheet1").Range("A2:A65536")
Set myRowRng = Sheets("Sheet1").Range("A2:A120")
For Each Cellr In myRowRng
r = r + 1
Cellr.Value = "Row: " & r
Next Cellr

Sheets("Sheet1").Cells.Select
Selection.Columns.AutoFit
Selection.Rows.AutoFit

Columns("A:A").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Interior.ColorIndex = 34

Rows("1:1").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Interior.ColorIndex = 34
Sheets("Sheet1").Range("A1").Select

Else
ActiveWindow.DisplayHeadings = True
End If
End Sub

Sub reSetHeaders()
ActiveWindow.DisplayHeadings = True
Sheets("Sheet1").Columns("A:A").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents

Sheets("Sheet1").Rows("1:1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
Sheets("Sheet1").Range("A1").Select
End Sub
 
Upvote 0
See the post above for all information!
The only change in the code below is an adjustment to the label counters, they now match the actual Row & Column headers in placement.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'ThisWorkbook code!
Dim c&, r&

If Sh.Name = "Sheet1" Then
ActiveWindow.DisplayHeadings = False

'Set myColRng = Sheets("Sheet1").Range("B1:IV1")
Set myColRng = Sheets("Sheet1").Range("B1:AA1")
c = 1
For Each Cellc In myColRng
c = c + 1
Cellc.Value = "Column: " & c
Next Cellc

'Set myRowRng = Sheets("Sheet1").Range("A2:A65536")
Set myRowRng = Sheets("Sheet1").Range("A2:A120")
r = 1
For Each Cellr In myRowRng
r = r + 1
Cellr.Value = "Row: " & r
Next Cellr

Sheets("Sheet1").Cells.Select
Selection.Columns.AutoFit
Selection.Rows.AutoFit

Columns("A:A").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideHorizontal).Weight = xlThin
Selection.Interior.ColorIndex = 34

Rows("1:1").Select
Selection.Borders(xlEdgeLeft).Weight = xlThin
Selection.Borders(xlEdgeTop).Weight = xlThin
Selection.Borders(xlEdgeBottom).Weight = xlThin
Selection.Borders(xlEdgeRight).Weight = xlThin
Selection.Borders(xlInsideVertical).Weight = xlThin
Selection.Interior.ColorIndex = 34
Sheets("Sheet1").Range("A1").Select

Else
ActiveWindow.DisplayHeadings = True
End If
End Sub

Sub reSetHeaders()
ActiveWindow.DisplayHeadings = True
Sheets("Sheet1").Columns("A:A").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents

Sheets("Sheet1").Rows("1:1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Selection.ClearContents
Sheets("Sheet1").Range("A1").Select
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Keep text within a merged row centered on screen when scrolling horizontally
Replies
1
Views
101
ISY
I
T
  • Solved
pivot table from columns with strings separated by commas
Replies
7
Views
303
Fluff
Fluff
E
  • Question
Does Excel have anything like Smartsheet's Hierarchy (Parent/Child rows)?
Replies
3
Views
404
Edgar_
Edgar_
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
478
kevin9999
K
F
  • Question
VBA code needs refined
Replies
2
Views
186
farr80
F

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top