Excel VBA - select multiple columns not in sequential order

Question

I would like to select multiple columns   Eg  I want to select column a  b  d  e  g  h  I ve tried   Columns  A  B  D  E  G  H   select   I get error message  Type mismatch

User · Answer

Some things of top of my head   Method 1   Application Union Range  a1    Range  b1    Range  d1    Range  e1    Range  g1    Range  h1    EntireColumn Select   Method 2   Range  a1 b1 d1 e1 g1 h1   EntireColumn Select   Method 3   Application Union Columns  a    Columns  b    Columns  d    Columns  e    Columns  g    Columns  h    Select

User · Answer

Range  A B D E G H   Select  can help  Edit note  I just saw you have used different column sequence  I have updated my answer

User · Answer

As a recorded macro   range  A A  B B  D D  E E  G G  H H   select

User · Answer

Some of the code looks a bit complex to me  This is very simple code to select only the used rows in two discontiguous columns D and H  It presumes the columns are of unequal length and thus more flexible vs if the columns were of equal length   As you most likely surmised 4 column D and 8 column H  Dim dlastRow As Long Dim hlastRow As Long  dlastRow   ActiveSheet Cells Rows Count  4  End xlUp  Row hlastRow   ActiveSheet Cells Rows Count  8  End xlUp  Row Range  D2 D   amp  dlastRow  amp    H2 H   amp  hlastRow  Select   Hope you find useful - DON T FORGET THAT COMMA BEFORE THE SECOND COLUMN  AS I DID  OR IT WILL BOMB

User · Answer

Working on a project I was stuck for some time on this concept - I ended up with a similar answer to Method 1 by  GSerg that worked great   Essentially I defined two formula ranges  using a few variables  and then used the Union concept   My example is from a larger project that I m working on but hopefully the portion of code below can help some other people who might not know how to use the Union concept in conjunction with defined ranges and variables   I didn t include the entire code because at this point it s fairly long - if anyone wants more insight feel free to let me know   First I declared all my variables as Public  Then I defined set each variable  Lastly I set a new variable  SelectRanges  as the Union between the two other FormulaRanges  Public r As Long Public c As Long Public d As Long Public FormulaRange3 As Range Public FormulaRange4 As Range Public SelectRanges As Range  With Sheet8       c   pvt DataBodyRange Columns Count   1    d   3    r    Cells  Rows Count  1  End xlUp  Row  Set FormulaRange3    Range  Cells d  c   2    Cells r - 1  c   2       FormulaRange3 NumberFormat    0      Set FormulaRange4    Range  Cells d  c   c   2    Cells r - 1  c   c   2       FormulaRange4 NumberFormat    0      Set SelectRanges   Union FormulaRange3  FormulaRange4

[excel] Excel VBA - select multiple columns not in sequential order

Examples related to excel

Examples related to vba