PDA

View Full Version : Solved: delete columns with an array collection



mperrah
07-23-2007, 12:49 AM
I was trying to clean up some recorded macros and found this method promising, not sure how it works.
Can I delete several columns at once?
- whats the format for the procedure?
below I have the columns I am deleting from right to left
the end of each line is what I calculated the total number of columns for each contiguous range.


Sub test_delete_raw()
Dim Arr
Arr = Array(1, 3, 5, 7, 9) ' 1:2, 4:5, 7:8, 10, 15, 20:44, 46:53, 55:256?
'or right to left - 256:55, 53:46, 44:20, 15, 10, 8:7, 5:4, 2:1
For i = 256 To 0 Step -1
Columns(Arr(i)).Delete
Next
End Sub

column name - total cols - from "A" - (Range from col 256)

Columns("BC:IV").Select - 202 - 54 - (55-256)
Columns("AT:BA").Select 8 - 46 - (46-53)
Columns("T:AR").Select - 25 - 21 - (20-44)
Columns("O:O").Select - 1 - 20 - (15)
Columns("J:J").Select - 1 - 19 - (10)
Columns("G:H").Select - 2 - 17 - (7-8)
Columns("D:E").Select - 2 - 15 - (4-5)
Columns("A:B").Select - 2 - 13 - (1-2)

xld
07-23-2007, 01:16 AM
Sub test_delete_raw()
Dim Arr
Dim i As Long
Arr = Array("A:B", "D:E", "G:H", "J:J", "T:AR", "AT:BA", "BC:IV")
For i = UBound(Arr) To LBound(Arr) Step -1
Columns(Arr(i)).Delete
Next
End Sub

rory
07-23-2007, 05:32 AM
Just for information, you don't actually need the array for this - you can use something like:
Range("A:B,D:E,G:H,J:J,T:AR,AT:BA,BC:IV").EntireColumn.Delete

FWIW.
Rory

mperrah
07-23-2007, 06:25 PM
Does this shift the remaining columns left after deleting?
So when the old columns are gone there will be 13 columns left
with out spaces?
Mark

mperrah
07-23-2007, 06:36 PM
Got it, Thanks
Rows("1:1").Delete
Range("A:B,D:E,G:H,J:J,O:O,T:AR,AT:BA,BC:IV").EntireColumn.Delete OMG that is sweet!
Thank you thank you!
Mark

sub thanks()
Dim help as variant

With help
.yourock
End With ' never

End sub
:)