Reptitive Header in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2416085%22%20slang%3D%22en-US%22%3EReptitive%20Header%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416085%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20have%20written%20VBA%20code%20and%20it%20works%20perfectly.%3C%2FP%3E%3CP%3EBut%20i%20press%20RUN%20button%20multiple%20times%20so%20it%20repeat%20headers%20in%20evry%20sheet.%3C%2FP%3E%3CP%3ESo%2C%20how%20to%20revert%20it%20back%20with%20the%20help%20of%20VBA%20coding%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attached%20file...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2416085%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2416163%22%20slang%3D%22en-US%22%3ERe%3A%20Reptitive%20Header%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%201%20-%20you%20have%20to%20do%20this%20only%20once%2C%20so%20you%20can%20do%20it%20manually%3A%3C%2FP%3E%0A%3CP%3ERemove%20the%20header%20rows%20from%20all%20sheets.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%202%20-%20adjust%20the%20macro%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPublic%20Sub%20CleanUpData()%0A%20%20%20%20Dim%20i%20As%20Integer%0A%20%20%20%20i%20%3D%201%0A%20%20%20%20Do%20While%20i%20%26lt%3B%3D%20Worksheets.Count%0A%20%20%20%20%20%20%20%20Worksheets(i).Select%0A%20%20%20%20%20%20%20%20If%20Range(%22A1%22).Value%20%26lt%3B%26gt%3B%20%22REGION%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20AddHeaders%0A%20%20%20%20%20%20%20%20%20%20%20%20FormatHeaders%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20Loop%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2416174%22%20slang%3D%22en-US%22%3ERe%3A%20Reptitive%20Header%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2416174%22%20slang%3D%22en-US%22%3ESir%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20Range(%22A1%22).Value%20%26lt%3B%26gt%3B%20%22REGION%22%20Then%3CBR%20%2F%3E%3CBR%20%2F%3Emeans%20that%20it%20prevent%20from%20coming%20multiple%20header..%3CBR%20%2F%3ERight%20sir%3F%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20we%20delete%20repititive%20header%20with%20the%20help%20of%20VBA%20code%3F%3C%2FLINGO-BODY%3E
Regular Contributor

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?

 

Please help?

 

Here is a attached file...

4 Replies

@Zan_Hanifee 

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
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?

@Zan_Hanifee 

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
Thank you so much sir