I’m devising code to open an Excel file, such as the one I’ve attached, delete every other column starting from the P column and save the file.
Is there a more elegant way that would avoid having to list the column names beforehand?
Code:
Sub DeleteColumns()
Dim xlApp As Excel.Application
Dim xlWbk As Excel.Workbook
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
If xlApp Is Nothing Then
MsgBox "Cannot open Excel.", vbExclamation
Exit Sub
End If
blnStart = True
End If
On Error GoTo ErrHandler
If xlApp.Dialogs(xlDialogOpen).Show = False Then
GoTo ExitHandler
End If
Set xlWbk = xlApp.ActiveWorkbook
xlApp.ScreenUpdating = False
Dim Cols(1 To m) As String, i As Long, m As Long
m = xlWbk.Worksheets(1).Range("A65536").End(xlUp).Column
Cols(1) = "P"
Cols(2) = "R"
Cols(3) = "T"
Cols(4) = "V"
Cols(5) = "X"
Cols(6) = "Z"
Cols(7) = "AB"
Cols(8) = "AD"
Cols(9) = "AF"
Cols(10) = "AH"
Cols(11) = "AJ"
Cols(12) = "AL"
Cols(13) = "AN"
Cols(14) = "AP"
Cols(15) = "AR"
Cols(16) = "AT"
Cols(17) = "AV"
Cols(18) = "AX"
Cols(19) = "AZ"
Cols(20) = "BB"
Cols(21) = "BD"
Cols(22) = "BF"
Cols(23) = "BH"
Cols(24) = "BJ"
Cols(25) = "BL"
Cols(26) = "BN"
Cols(27) = "BP"
Cols(28) = "BR"
Cols(29) = "BT"
Cols(30) = "BV"
Cols(31) = "BX"
Cols(32) = "BZ"
Cols(33) = "CB"
For i = 1 To m
Columns(Cols(i)).Delete
Next i
xlApp.ScreenUpdating = True
ExitHandler:
On Error Resume Next
xlWbk.Close SaveChanges:=True
Set xlWbk = Nothing
If blnStart Then
xlApp.Quit
End If
Set xlApp = Nothing
'Screen.ActiveForm.Repaint
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
