Results 1 to 4 of 4

Thread: Delete columns macro
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    372
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Delete columns macro

    How can I create a macro to delete many columns.

    I need the below columns deleted. I tried but failed....

    Range("B:S,C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW :AW,AY:AY, _
    BA:BA,BC:BC,BE:BE,BG:BG,BI:BI,BK:BK,BM:BM,BO:BO,BQ:BQ,BS:BS,BU:BU,BW:BW,BY:BY,CA:CA,CC:CC,CE:CE,CG:CG,CI:CI,CK:CK,CM:CM, CO:CO, _
    CQ:CQ,CS:CS,CU:CU,CW:CW,CY:CY,DA:DA,DC:DC,DE:DE,DG:DG,DI:DI,DK:DK,DM:DM,DO:DO,DQ:DQ,DS:DS,DU:DU,DW:DW,DY:DY,EA:EA,EC:EC, EE:EE, _
    EG:EG,EI:EI,EK:EK,EM:EM,EO:EO,EQ:EQ,ES:ES,EU:EU,EW:EW,EY:EY,FA:FA,FC:FC,FE:FE,FG:FG,FI:FI,FK:FK,FM:FM,FO:FO,FQ:FQ,FS:FS, FU:FU,FW:FW,FY:FY, _
    GA:GA,GC:GC,GE:GE,GG:GG,GI:GI,GK:GK,GM:GM,GO:GO,GQ:GQ,GS:GS,GU:GU,GW:GW,GY:GY,HA:HA,HC:HC,HE:HE,HG:HG,HI:HI,HK:HK,HM:HM, HO:HO,HQ:HQ,HS:HS, _
    HU:HU,HW:HW,HY:HY,IA:IA,IC:IC,IE:IE,IG:IG,II:II,IK:IK,IM:IM,IO:IO,IQ:IQ,IS:IS,IU:IU,IW:IW,IY:IY,JA:JA,JC:JC,JE:JE,JG:JG, JI:JI,JK:JK,JM:JM, _
    JO:JO,JQ:JQ,JS:JS,JU:JU,JW:JW,JY:JY,KA:KA,KC:KC,KE:KE,KG:KG,KI:KI,KK:KK,KM:KM,KO:KO,KQ:KQ,KS:KS,KU:KU,KW:KW,KY:KY,LA:LA, LC:LD,LF:LG,LI:LJ, _
    LL:LM,LO:LP,LR:LS,LU:LU,LW:LW,LY:LY,MA:MA,MC:MC,ME:ME,MG:MG,MI:MI,MK:MK,MM:MM,MO:MO,MQ:MQ,MS:MS,MU:MU,MW:MW,MY:MY,NA:NA, NC:NC,NE:NE,NG:NG, _

    NI:NI,NK:NK,NM:NM,NO:NO,NQ:NQ,NS:NS,NT:NT").Select

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,759
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete columns macro

    Try using Application.Union

    Look at this link https://docs.microsoft.com/en-us/off...lication.union
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    New Member
    Join Date
    Apr 2008
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete columns macro

    The line continuation character ( _) doesn't work with strings.

    See here: https://docs.microsoft.com/en-us/off...tion-character

  4. #4
    New Member
    Join Date
    Apr 2008
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete columns macro

    This probably will do it although it's quite clunky:

    Code:
    Sub DeleteAll()
    
    Set rng1 = Range("B:B,C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY")
    Set rng2 = Range("BA:BA,BC:BC,BE:BE,BG:BG,BI:BI,BK:BK,BM:BM,BO:BO,BQ:BQ,BS:BS,BU:BU,BW:BW,BY:BY,CA:CA,CC:CC,CE:CE,CG:CG,CI:CI,CK:CK,CM:CM,CO:CO")
    Set rng3 = Range("CQ:CQ,CS:CS,CU:CU,CW:CW,CY:CY,DA:DA,DC:DC,DE:DE,DG:DG,DI:DI,DK:DK,DM:DM,DO:DO,DQ:DQ,DS:DS,DU:DU,DW:DW,DY:DY,EA:EA,EC:EC,EE:EE")
    Set rng4 = Range("EG:EG,EI:EI,EK:EK,EM:EM,EO:EO,EQ:EQ,ES:ES,EU:EU,EW:EW,EY:EY,FA:FA,FC:FC,FE:FE,FG:FG,FI:FI,FK:FK,FM:FM,FO:FO,FQ:FQ,FS:FS,FU:FU,FW:FW,FY:FY")
    Set rng5 = Range("GA:GA,GC:GC,GE:GE,GG:GG,GI:GI,GK:GK,GM:GM,GO:GO,GQ:GQ,GS:GS,GU:GU,GW:GW,GY:GY,HA:HA,HC:HC,HE:HE,HG:HG,HI:HI,HK:HK,HM:HM,HO:HO,HQ:HQ,HS:HS")
    Set rng6 = Range("HU:HU,HW:HW,HY:HY,IA:IA,IC:IC,IE:IE,IG:IG,II:II,IK:IK,IM:IM,IO:IO,IQ:IQ,IS:IS,IU:IU,IW:IW,IY:IY,JA:JA,JC:JC,JE:JE,JG:JG,JI:JI,JK:JK,JM:JM")
    Set rng7 = Range("JO:JO,JQ:JQ,JS:JS,JU:JU,JW:JW,JY:JY,KA:KA,KC:KC,KE:KE,KG:KG,KI:KI,KK:KK,KM:KM,KO:KO,KQ:KQ,KS:KS,KU:KU,KW:KW,KY:KY,LA:LA,LC:LD,LF:LG,LI:LJ")
    Set rng8 = Range("LL:LM,LO:LP,LR:LS,LU:LU,LW:LW,LY:LY,MA:MA,MC:MC,ME:ME,MG:MG,MI:MI,MK:MK,MM:MM,MO:MO,MQ:MQ,MS:MS,MU:MU,MW:MW,MY:MY,NA:NA,NC:NC,NE:NE,NG:NG")
    Set rng9 = Range("NI:NI ,NK:NK ,NM:NM ,NO:NO , NQ:NQ, NS:NS , NT:NT")
    
    rng1.EntireColumn.Delete
    rng2.EntireColumn.Delete
    rng3.EntireColumn.Delete
    rng4.EntireColumn.Delete
    rng5.EntireColumn.Delete
    rng6.EntireColumn.Delete
    rng7.EntireColumn.Delete
    rng8.EntireColumn.Delete
    rng9.EntireColumn.Delete
    
    End Sub
    Last edited by deadlyduck; May 17th, 2019 at 04:33 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •