none
using variables in multiple column select RRS feed

  • Question

  • Can someone explaing to me why this will select two seperate columns

    'select Columns C & G

    Range("C7:C300,G7:G300").Select

    but this will select the two columns and all of the columns between:

    Dim rngEnd As Long

    'find last row with data in it
        rngEnd = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    'select columns C & G
        Range("C7:C" & rngEnd, "G7:G" & rngEnd).Select

     What I am doing is finding the last row with data in it and using that as a variable in the row part of the range select

    Thursday, January 3, 2013 4:32 PM

Answers

  • In the first example, you provide one argument: "C7:C300,G7:G300".

    In the second example, you provide two arguments: "C7:Cn" and "G7:Gn", separated by a comma. If you want the same effect as the first example, use

        Range("C7:C" & rngEnd & ",G7:G" & rngEnd).Select


    Regards, Hans Vogelaar

    • Marked as answer by dhnobles Thursday, January 3, 2013 6:08 PM
    Thursday, January 3, 2013 4:59 PM

All replies

  • In the first example, you provide one argument: "C7:C300,G7:G300".

    In the second example, you provide two arguments: "C7:Cn" and "G7:Gn", separated by a comma. If you want the same effect as the first example, use

        Range("C7:C" & rngEnd & ",G7:G" & rngEnd).Select


    Regards, Hans Vogelaar

    • Marked as answer by dhnobles Thursday, January 3, 2013 6:08 PM
    Thursday, January 3, 2013 4:59 PM
  • Thanks Hans,

    That worked perfectly, I knew I was close but couldn't quite put my finger on it. So if I want the line to be two seperate arguments the the argument seperator (the comma) needs to be between the two quotations and preceded with the ampersand (&) but if I put the comma inside the second quotation and drop the additional ampersand VBA treats it as a continuation of the same argument.

    Thursday, January 3, 2013 6:07 PM