deleting specific XLS columns by macro 2

[flashbbeb]

Hi all,

Novice coder here! I'm trying to create a macro that deletes specific columns - just by which one, not even by criteria like a cell value.

I'd like to be able to do this with an array where I specify the columns, but I don't know how to loop through each slot in the array to delete the column. If there's a better way to do this, I'd appreciate that, too!

I've gotten as far as declaring the array:
Dim coldelArray() As Variant
coldelArray = Array(3, 5, 8, 10, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)

And the function to delete a column:

Columns(however I get the value from the array).EntireColumn.Delete

Thanks!
EB

 

[SkipVought]

Hi,

Most of the time, it is much better to have data (like a list of column numbers) in a table, rather than hard coded.

I'd put this list in a table on a sheet. Then loop thru the table from the highest value to the lowest value

dim r as range
'assume that your list is on Sheet2, beginning in A1, with no heading...
Sheet2.Sort Key1:=Sheet2.Range("A1"), Order1:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

'assume your columns are on Sheet1
for each r in Sheet2.Range(Sheet2.[A1], Sheet2.[A1].end(xldown))
  Sheet1.columns(r.value).delete xlleft
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

A starting point:

Dim coldelArray(), colnum
coldelArray = Array(3, 5, 8, 10, 12, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26)
For Each colnum In coldelArray
  Columns(colnum).EntireColumn.Delete
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

PHV,

BIG problem. When column 3 is deleted, column 5 becomes column 4, etc.

TILT!

Hence you must loop, highest to lowest.

Use PHV's solution, but reverse your array values.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

OOps, good catch Skip :~/

 

[flashbbeb]

Thanks Skip and PHV.

For further clarification, I'm using this macro on a file I receive every day that's formatted the same way - same columns in the same order every time. So in my particular case, hard coding the column numbers into an array is fine with me.

So in the end - PHV's array with Skip's order (highest to lowest) works perfectly!