CODEWITHSUNDEEP
excelvba
AP1979
AP1979

Reputation: 9

Renaming column header in Excel

I have same columns repeating within the worksheet such as

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!

Upvotes: 0

Views: 5890

Answers (3)

AP1979
AP1979

Reputation: 9

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

Upvotes: 0

Mrig
Mrig

Reputation: 11702

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

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

Upvotes: 1

Related Questions