delete every other column...


Ad

Advertisements

C

Chip Pearson

Try something like the following:

Dim ColNdx As Integer
For ColNdx = Columns.Count To 1 Step -2
Cells(1, ColNdx).EntireColumn.Delete
Next ColNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
S

Scottie

I'm so proud of myself. Created the macro and it works
however want it to just run over selected columns. Am I
being greedy? (Second time I've used the newsgroup and
second time its worked... can you explain more about the
code choices?)
THX Chip!
 
C

Chip Pearson

Scottie,

Try the following code:

Dim ColNdx As Integer
Dim StartCol As Integer
Dim EndCol As Integer
StartCol = Selection(1, 1).Column
EndCol = Selection(Selection.Cells.Count).Column
For ColNdx = EndCol To StartCol Step -2
Cells(1, ColNdx).EntireColumn.Delete
Next ColNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Ad

Advertisements

D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top