Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:
    Code:
    	For i = xlWbk.Worksheets(1).Columns.Count to 16 Step -2
    	  xlWbk.Worksheets(1).Columns(i).Delete
    	Next i
    Note: you don't have to use Tab tags within a Code ... /Code block. Spaces are displayed "as is".

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •