Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Renaming colums

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,063
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default


    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?
    See the Excel Help Index topic for "Keep row and column labels visible as you scroll".

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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;

    ****** bgcolor="#9F9F9F" >

    ABCDE
    1
    Test1Test2Test3Test4
    21



    32



    43



    54



    65



    76



    87



    98



    109



    1110



    1211




    12








    _________________
    Kind Regards,
    Ivan F Moala
    http://www.gwds.co.nz/excel_files.html - Under Constru

    [ This Message was edited by: Ivan F Moala on 2002-04-20 15:33 ]

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

  7. #7
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    JSW: Try and try again: "The way of the Coder!"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •