Reptitive Header in VBA

Hello Everyone, I have written VBA code and it works perfectly. But i press RUN button multiple times so it repeat headers in evry sheet. So, how to revert it back with the help of VBA coding? ...

ZanCleaningUpData.xlsm30 KB

Macros and VBA

HansVogelaar

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

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

Excel
Iron Contributor
Jun 04, 2021
Thank you so much sir😊😊

Forum Discussion

Reptitive Header in VBA

Resources