SOLVED

Reptitive Header in VBA

Iron 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

@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
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?
best response confirmed by allyreckerman (Microsoft)
Solution

@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
Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@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

View solution in original post