Renaming column header in Excel

Question

I have same columns repeating within the worksheet such as

Unit ID
Unit Name

several times

I want these column headers to be changed based on their occurrences.
For eg. first occurrence of Unit ID will be replaced as Unit ID_1 and second occurrence will be renamed as Unit ID_2 and so on.
Any help will be fab!

I have tried below code but the same does not seem to be working and also does not throw any error - — AP1979, Jul 27 '17 at 09:11

score 1 · Answer 1 · answered Jul 27 '17 at 09:19

1

You have this headers in a row, right? Declare some integer variable: Dim i As Integer and set it to 1 i = 1, go through that row in a loop, each time you come upon header you specify, you increment i and append it to the name:

If Cells(1, j).Value = "Unit ID" Then 'assumed that first row contains headers and j is loop variable
    Cells(1, j).Value = Cells(1, j).Value & "_" & i
    i = i + 1
End If

answered Jul 27 '17 at 09:19

Michał Turczyn

32,028
14
47
69

1

Note here is a good text about why you should [always use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613). – Pᴇʜ Jul 27 '17 at 09:33

score 1 · Answer 2 · answered Jul 27 '17 at 09:35

Try this:

Option Explicit

Sub Demo()
    Dim lastCol As Long, idCount As Long, nameCount As Long, headerRow As Long
    Dim rng As Range, cel As Range

    headerRow = 1       'row number with headers
    lastCol = Cells(headerRow, Columns.Count).End(xlToLeft).Column 'last column in header row
    idCount = 1
    nameCount = 1
    Set rng = Sheets("Sheet1").Range(Cells(headerRow, 1), Cells(headerRow, lastCol)) 'header range

    For Each cel In rng                     'loop through each cell in header
        If cel = "Unit ID" Then             'check if header is "Unit ID"
            cel = "Unit ID_" & idCount      'rename "Unit ID" using idCount
            idCount = idCount + 1           'increment idCount
        ElseIf cel = "Unit Name" Then       'check if header is "Unit Name"
            cel = "Unit Name_" & nameCount  'rename "Unit Name" using nameCount
            nameCount = nameCount + 1       'increment nameCount
        End If
    Next cel
End Sub

as we have not defined it. Hence the program runs without an error but does not change the value — AP1979, Jul 27 '17 at 11:03
@AP1979- `cel` is a variable of type `Range`. Its declared at the top of code, see `Dim rng As Range, cel As Range`. — Mrig, Jul 27 '17 at 11:05

score 0 · Accepted Answer · answered Aug 30 '18 at 10:43

Got answer to this, below is the code -

    Dim wbCurrent As Workbook
    Dim wsCurrent As Worksheet
    Dim nLastCol, i, j, k As Integer
    Set wbCurrent = ActiveWorkbook
    Set wsCurrent = wbCurrent.ActiveSheet
    nLastCol = wsCurrent.Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    j = 0
For i = nLastCol To 1 Step -1
    j = j + 1
   ' k = InStr(1, wsCurrent.Cells(1, i).Value, "Sweep TID", vbTextCompare) > 0
        If InStr(1, wsCurrent.Cells(1, i).Value, "unitid", vbTextCompare) = 0 Then
            wsCurrent.Cells(1, i).Value = "unitid_" & j
        End If
        If InStr(1, wsCurrent.Cells(1, i).Value, "UnitName", vbTextCompare) > 0 _
        Then
            wsCurrent.Cells(1, i).Value = "UnitName_" & j
        End If
    Next i

The above code identifies the last column with the data in it and uses it in loop

Renaming column header in Excel

3 Answers3