Deleting more than 20 columns in excel vba

Question

I wrote a macro to do multiple things and in the end want to delete more than 20 columns.
I used the below statement to execute it but it gives me a run time error. It looks like there is a limit on how many columns you can delete at a time.

I recorded a macro to delete all the below columns and it seems that excel is splitting the range statement into multiple but when i use it, it does not seem to work. pls let me know what's the easiest way to delete so many columns. thanks

Range("A:A,C:C,D:D,E:E,F:F,J:J,L:L,M:M,O:O,Q:Q,R:R,T:T,U:U,V:V,W:W,X:X,Z:Z,AA:AA,AB:AB,AC:AC,AD:AD,AE:AE,AG:AG,AI:AI,AK:AK,AL:AL,AN:AN,AO:AO,AP:AP,AQ:AQ,AR:AR,AT:AT,AU:AU, AV: AV , AW: AW , AX: AX , AY: AY , AZ: AZ , BA: BA , BB: BB , BC: BC , BD: BD , BE: BE , BF: BF , BG: BG , BH: BH, BI:     BI , BJ: BJ , BK: BK , BM: BM , BN: BN , BP: BP , BQ: BQ , BR: BR , BS: BS , BU: BU , BV: BV , BW: BW , BX: BX , BY: BY , CA: CA, CB:     CB , CC: CC , CE: CE ").Select
Range("CE1").Activate
Selection.Delete Shift:=xlToLeft

hd1 · Accepted Answer · 2013-06-05 20:40:49Z

3

With Sheets(shtname)
        .Columns(Range("A").Select).EntireColumn.Delete
        .Columns(Range("C:F,J,L,M,O").Select).EntireColumn.Delete
        .Columns(Range("Q:R, T:X").Select).EntireColumn.Delete
        .Columns(Range("Z:AE,AI,AK:AL,AN:AR,AT:BK").EntireColumn.Delete
' complete your range in the same way and you should be sorted
End With

Seems like it should work for your purpose, if not, do leave a comment clarifying your question.

edited Jun 5, 2013 at 20:40

answered Jun 4, 2013 at 22:18

hd1

34.9k5 gold badges83 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sous2817 Over a year ago

just trying to avoid the inevitable post back that "it didn't do what I wanted!". Removed my old comment because it's now miss leading...+1 none the less.

user2453587 Over a year ago

thanks a lot guys but as you mentioned, it did not work. gives me run time error 13. type mismatch. i wanted to do it in the activesheet. so yes if you could please let me know where to put leading +1, that would be great. thanks.

hd1 Over a year ago

Application.ActiveSheet.name should address that for you

user2453587 Over a year ago

thanks. now below statement gives me run time error '1004'. Method 'Range' of object'_global' failed. looks like i need to split it into multiple statements using a union. pls let me know how to break it into multiple ranges and join them using union. With Application.ActiveSheet .Columns(Range("A,C:F,J,L:M,O,Q:R,T:X,Z:AE,AG,AI,AK:AL,AN:AR,AT:BK,BM:BN,BP:BS,BU:BY,CA:CC,CE").Select).EntireColumn.Delete End With

JosieP · Accepted Answer · 2013-06-04 22:45:12Z

2

The address string you pass to the range property can't exceed 255 characters. You can shorten yours because some of the columns are adjacent -eg use T:X rather than T:T,U:U,V:V,W:W,X:X. If the string is still too long you will need to create separate ranges and union them, or perform the delete in a couple of steps.

edited Jun 4, 2013 at 22:45

answered Jun 4, 2013 at 22:17

JosieP

3,4101 gold badge15 silver badges16 bronze badges

3 Comments

user2453587 Over a year ago

is there a particular syntax for union that i need to follow?

JosieP Over a year ago

it would be union(range("a:d,f:h"), range("y:aa,ad:bq")).entirecolumn.delete for instance

user2453587 Over a year ago

thanks guys for all your help. below is what worked for me.. hopefully it would be helpful for someone else looking for similar question... Range("A1").Select Union(Range("A:A,C:F,J:J,L:M,O:O"), Range("Q:R,T:X"), Range("Z:AE, AG:AG"), Range("AI:AI,AK:AL,AN:AR,AT:BK"), Range("BM:BN,BP:BS,BU:BY"), Range("CA:CC,CE:CE")).Select Range("CE1").Activate Selection.Delete Shift:=xlToLeft

Collectives™ on Stack Overflow

Deleting more than 20 columns in excel vba

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related