Public Sub CleanUpData()
    Dim i As Integer
    i = 1
    Do While i <= Worksheets.Count
        Worksheets(i).Select
        Do While Range("A2").Value = "REGION"
            Range("A2").EntireRow.Delete
        Loop
        If Range("A1").Value <> "REGION" Then
            AddHeaders
            FormatHeaders
        End If
        i = i + 1
    Loop
End Sub

4 Replies

HansVogelaar

MVP

Jun 04, 2021

Excel

Step 1 - you have to do this only once, so you can do it manually:

Remove the header rows from all sheets.

Step 2 - adjust the macro:

Public Sub CleanUpData()
    Dim i As Integer
    i = 1
    Do While i <= Worksheets.Count
        Worksheets(i).Select
        If Range("A1").Value <> "REGION" Then
            AddHeaders
            FormatHeaders
        End If
        i = i + 1
    Loop
End Sub

Excel

Iron Contributor

Jun 04, 2021

Sir,

If Range("A1").Value <> "REGION" Then

means that it prevent from coming multiple header..
Right sir?

Can we delete repititive header with the help of VBA code?

HansVogelaar

MVP

Jun 04, 2021

Excel

Yes:

Public Sub CleanUpData()
    Dim i As Integer
    i = 1
    Do While i <= Worksheets.Count
        Worksheets(i).Select
        Do While Range("A2").Value = "REGION"
            Range("A2").EntireRow.Delete
        Loop
        If Range("A1").Value <> "REGION" Then
            AddHeaders
            FormatHeaders
        End If
        i = i + 1
    Loop
End Sub