Here's one way:
Option Explicit
Sub deleteEveryOtherColumnInSelection()
Dim ColNdx As Integer
If Selection.Areas.Count > 1 Then
MsgBox "Please, just one area in your selection"
Exit Sub
End If
If Selection.Columns.Count Mod 2 <> 0 Then
MsgBox "Please select an even number of columns"
Exit Sub
End If
With Selection
For ColNdx = .Cells(.Cells.Count).Column To .Cells(1).Column Step -2
ActiveSheet.Cells(1, ColNdx).EntireColumn.Delete
Next ColNdx
End With
End Sub
It checks to see if you've selected more than one area and stops if you did.
It checks to see if you selected an even number of columns. If you selected
A:C, would you want to delete A & C and keep B or delete B.
In this routine, I kept the first column in the selection and deleted the next
(and so on).
Then the part that does the real work:
The last cell's column in your selection can be gotten by:
selection.cells(selection.cells.count).column
(I used the with statement to make typing easier).
So I pick up the column of that last cell and cycle back every other column
(Step -2) until I get to the first column in the selection:
selection.cells(1).column
And Chip used ColNdx as a variable to represent the Column Index. He declared
it as Integer since he knows that there are only 256 columns in the worksheet.
If he were doing rows, I'm betting he would have used:
dim rowNdx as Long
long's can count higher than integers
Integers: -32,768 to 32,767
Longs: -2,147,483,648 to 2,147,483,647
(from VBA's help. I put the cursor on Integer and hit F1--then same for Long.)
And current versions of excel have 64k rows.