Deleting evey nth column

  • Thread starter Thread starter Darin Kramer
  • Start date Start date
D

Darin Kramer

HI there,

A tough one I think...

I would like to keep column A,B,C.
Then delete Column D,E,F, keep column G
Then delete column h,i,j keep column K
Then delete column l,m,n keep column O, etc etc till I run out of
columns.

I was previously helped with a SIMILAR problem (the movement of the
number of columns was different and the VB below worked for me, however
I dont know how to change it to achieve what I want to achieve above.
Any ideas????

Sub go_delete()
Dim tmp As Integer
Dim tmp2 As Integer
Dim tmp_address As String, row_count As Single

With ActiveSheet.UsedRange
tmp = Fix((.Columns.Count - 5) / 4)
If (.Columns.Count - 5) Mod 4 > 0 Then
tmp = tmp + 1
End If
tmp_address = .Address(False, False)
row_count = .Rows.Count
End With
With ActiveSheet.Range(tmp_address)
With .Offset(0, 5).Resize(row_count, .Columns.Count - 5)
For tmp2 = tmp To 1 Step -1
With .Columns(4 * tmp2 - 3)
.Resize(row_count, 2).EntireColumn.Delete
End With
Next
End With
End With
End Sub
 
Sub go_delete()
Dim tmp As Range
Dim i As Long

With ActiveSheet
Set tmp = .Range("D1:F1")
For i = 8 To .Columns.Count - 2
Set tmp = Union(.Cells(1, i).Resize(, 2), tmp)
Next i
End With

tmp.EntireColumn.Delete

End Sub



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Thanks Bob - unfortunately it doesnt work...
I changed my range from d1:f1 to say d1:ax1, but all it did was delete
everything except for column a,b, and c... am I doing something wrong?
do I need to change the i...?

Regards

D
 
The D1:F1 was just to initialise the tmp range.

You should use

Sub go_delete()
Dim tmp As Range
Dim i As Long

With ActiveSheet
Set tmp = .Range("D1:F1")
For i = 8 To .Range("AX1").Column - 2
Set tmp = Union(.Cells(1, i).Resize(, 2), tmp)
Next i
End With

tmp.EntireColumn.Delete

End Sub



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
If it's always those columns this should work
Sub go_delete(
Range("A1").Selec
Dim iColumn As Intege
iColumn =
D
Range(Cells(1, iColumn), Cells(1, iColumn
2).Address).EntireColumn.Delet
iColumn = iColumn +
Loop Until iColumn = 25
End Su

-Thanks Bob - unfortunately it doesnt work..
I changed my range from d1:f1 to say d1:ax1, but all it did was delet
everything except for column a,b, and c... am I doing something wrong
do I need to change the i...

Regard
 
Bob I guess my explanation of the problem wasnt clear enought, because I
know you would easily be able to solve it :) (and have done so in the
past!)

Basically Macro is still not working it works for the first instance, ie
it correctly deletes columns D,E and F (and now G is next to C), but
thats all it does, from H to AI nothing happens. (Does it matter that
H,L,P, etc are blank..?)
I need to delete H,I,J
Keep K
Delete L,M,N
Keep O
Delete P,Q,r
Keep S
etc

Appreciate your time and effort
 
Try this and let me know how you get on. It's flexible in that you can
alter the number of columns to delete and the number of columns to
skip. There is some redundancy in looping till 256 but it's still
quick and I am sure you could easily optimise it.

Sub Test()

DeleteNthColumns ThisWorkbook.Worksheets(1), 4, 3, 1

End Sub

Sub DeleteNthColumns(sht As Excel.Worksheet, lngFirstColumnToDelete As
Long, lngColumnsToDelete As Long, lngColumnsToKeep As Long)

Dim lngColCurrent As Long
Dim lngDeleteCounter As Long

'starting on the first column to delete
lngColCurrent = lngFirstColumnToDelete

Do

For lngDeleteCounter = 1 To lngColumnsToDelete

'delete as many columns as specified
sht.Columns(lngColCurrent).Delete

Next lngDeleteCounter

'then skip as many columns as specified
lngColCurrent = lngColCurrent + lngColumnsToKeep

Loop While lngColCurrent <= 256 'only 256 columns in an excel sheet

End Sub
 
No, my bad, you explained it fine

Try this alternative

Sub go_delete()
Dim tmp As Range
Dim i As Long

With ActiveSheet
Set tmp = .Range("D1:F1")
For i = 8 To .Range("AX1").Column - 2 Step 4
Set tmp = Union(.Cells(1, i).Resize(, 3), tmp)
Next i
End With

tmp.EntireColumn.Delete

End Sub



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Thanks Barry-Jon,

Are there parts that I need to get to customise to get it to run
currently, or should it just run? (after inserting it, and running it,
Excel thought for a long while, but to be honest, I cant see the
result... :) )
 
Have you got it working then? It should just work

I created a sub "DeleteNthColumns" that takes in paremeters of the
worksheet you want to perform the action on, the first column number to
delete (D=4), the number of columns to delete and the number of columns
to keep. The "Test" sub calls that procedure on the first worksheet
starting at column d, deleting three columns and keeping one.
 
Back
Top