Formatting the excel with VBA

[Abhishek A]

Hi
I want below formatting points in a single macro.. Could you please help me on this..

• Select all Rows starting at row 2 to the bottom
• Delete all rows that do not have “20” in column D
• Delete Columns H-AD
• Copy & Paste the following headers starting with the first date field (new column H)

DoB

DoH

Entry Date

Term Date

Disable Date

Death Date

Officer Staus Cd

Own%

YTD OverrideHrs

SrceLev Elig OptCd

Div

Term

Rehire

• Delete the following columns:
  • OfficerStausCd
  • Own%
  • YTDOverrideHrs
  • SrceLevEligOptCd
  • Div
• Save changes

 

[AlanSidman]

Option Explicit

Sub foo()
    Dim i As Long, lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    Set rng = Sheets("Sheet1").Range("A1:M1")
    Application.ScreenUpdating = False
    For i = lr To 2 Step -1
        If Range("D" & i) <> 20 Then
            Range("D" & i).EntireRow.Delete
        End If
    Next i

    Range("H1:AD1").EntireColumn.Delete
    rng.Copy Range("H1")
    Range("N1:R1").EntireColumn.Delete
    Application.ScreenUpdating = True
    MsgBox "complete"
End Sub

Assumes the Header you want inserted is sitting on a sheet named Sheet1 in the range area A1:M1