How can I rename each column of a table

0 votes

I want to give each column in a table with X columns its own name.

The string on A1 will be used to rename the first column, followed by the string on B1 for the second, and so on.

enter image description here

I tried using: ActiveWorkbook.Names.Add Name:=Name, RefersToR1C1:="=Sheet1!R2C1:R70C1"

But I want to replace the R2C1:R70C1 to something like R2CA:R70CA where A's value goes up for each column. And also replace Sheet1 with ActiveSheet.Name

Any suggestions?

Feb 7, 2023 in Others by Kithuzzz
• 38,010 points
266 views

1 answer to this question.

0 votes

Add Name For Each Column

Sub AddNames()
    
    Const FirstCol As String = "A"
    Const FirstRow As Long = 2
    Const LastRow As Long = 70
    
    With ActiveSheet

        Dim wsName As String: wsName = .Name

        Dim fCell As Range: Set fCell = .Cells(FirstRow, FirstCol)
        Dim rg As Range
        Set rg = .Range(fCell, .Cells(FirstRow, .Columns.Count).End(xlToLeft)) _
            .Resize(LastRow - FirstRow + 1)

        Dim crg As Range, ErrNumber As Long, nmName As String

        For Each crg In rg.Columns

            nmName = CStr(crg.Cells(1).Value)

            On Error Resume Next
                .Names.Add nmName, "'" & wsName & "'!" & crg.Address
                ErrNumber = Err.Number
            On Error GoTo 0

            If ErrNumber <> 0 Then
                MsgBox "Could not add name """ & nmName & """.", vbCritical
                ErrNumber = 0
            End If

        Next crg

    End With
        
    MsgBox "Names added.", vbInformation

End Sub
  • If you want the ranges of only the data (no headers), use the following:
Sub AddNamesData()
    
    Const FirstCol As String = "A"
    Const FirstRow As Long = 2
    Const LastRow As Long = 70
    
    With ActiveSheet
        
        Dim wsName As String: wsName = .Name
        
        Dim fCell As Range: Set fCell = .Cells(FirstRow, FirstCol)
        Dim rg As Range
        Set rg = .Range(fCell, .Cells(FirstRow, .Columns.Count).End(xlToLeft)) _
            .Resize(LastRow - FirstRow + 1)
        
        Dim hrg As Range: Set hrg = rg.Rows(1)
        Dim drg As Range: Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1)
        
        Dim hCell As Range, c As Long, ErrNumber As Long, nmName As String
        
        For Each hCell In hrg.Cells
            
            c = c + 1
            nmName = CStr(hCell.Value)
            
            On Error Resume Next
                .Names.Add nmName, "'" & wsName & "'!" & drg.Columns(c).Address
                ErrNumber = Err.Number
            On Error GoTo 0
            
            If ErrNumber <> 0 Then
                MsgBox "Could not add name """ & nmName & """.", vbCritical
                ErrNumber = 0
            End If
        
        Next hCell
    
    End With
        
    MsgBox "Names added.", vbInformation

End Sub
answered Feb 7, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,420 points
823 views
0 votes
1 answer

Excel-How can I get the address of a cell instead of a value?

There are various difficulties in this. Which ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,420 points
235 views
0 votes
1 answer

Given a column of int64, make a REST call for each value, return results to a single table

It would be best if you read. ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
264 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
833 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,132 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
430 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
692 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
483 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
965 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP