Select multiple cells in a column

  • Hello,


    i have a table of data (see below) from which i create a chart.


    the table can be any row length in cols b through i.


    if i select a cell from col b, (which labeled "SHAFT" in the data table below) which becomes the active cell, i have a macro which selects the rest of the row and creates a chart with the selected data.


    if i select select several contiguous cells from col b, i can plot the data using he same macro.


    however, if i select-non contiguous cells from col b, the macro just plots the data first active cell/row selected.


    my question: is how to select multiple non-contiguous cells from col b and plot the data from the rows selected?


    below is part of the macro:



    below is the data table


    [TABLE="width: 628"]

    [tr]


    [td][/td]


    [TD="colspan: 7"]DISTANCE FROM TIP END, inches[/TD]

    [/tr]


    [tr]


    [td]

    SHAFT

    [/td]


    [td]

    41

    [/td]


    [td]

    36

    [/td]


    [td]

    31

    [/td]


    [td]

    26

    [/td]


    [td]

    21

    [/td]


    [td]

    16

    [/td]


    [td]

    11

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber5 A

    [/td]


    [td]

    155

    [/td]


    [td]

    181

    [/td]


    [td]

    215

    [/td]


    [td]

    268

    [/td]


    [td]

    335

    [/td]


    [td]

    479

    [/td]


    [td]

    779

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber6 A

    [/td]


    [td]

    163

    [/td]


    [td]

    180

    [/td]


    [td]

    210

    [/td]


    [td]

    251

    [/td]


    [td]

    322

    [/td]


    [td]

    454

    [/td]


    [td]

    750

    [/td]


    [/tr]


    [tr]


    [td]

    X CaliberSL A

    [/td]


    [td]

    163

    [/td]


    [td]

    183

    [/td]


    [td]

    208

    [/td]


    [td]

    242

    [/td]


    [td]

    303

    [/td]


    [td]

    416

    [/td]


    [td]

    705

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber5 L

    [/td]


    [td]

    148

    [/td]


    [td]

    175

    [/td]


    [td]

    210

    [/td]


    [td]

    260

    [/td]


    [td]

    330

    [/td]


    [td]

    470

    [/td]


    [td]

    765

    [/td]


    [/tr]


    [tr]


    [td]

    X CaliberSL L

    [/td]


    [td]

    159

    [/td]


    [td]

    182

    [/td]


    [td]

    211

    [/td]


    [td]

    251

    [/td]


    [td]

    316

    [/td]


    [td]

    430

    [/td]


    [td]

    736

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber5 R

    [/td]


    [td]

    163

    [/td]


    [td]

    187

    [/td]


    [td]

    220

    [/td]


    [td]

    277

    [/td]


    [td]

    342

    [/td]


    [td]

    485

    [/td]


    [td]

    788

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber6 R

    [/td]


    [td]

    171

    [/td]


    [td]

    193

    [/td]


    [td]

    227

    [/td]


    [td]

    269

    [/td]


    [td]

    340

    [/td]


    [td]

    464

    [/td]


    [td]

    766

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber6+ R

    [/td]


    [td]

    168

    [/td]


    [td]

    190

    [/td]


    [td]

    227

    [/td]


    [td]

    278

    [/td]


    [td]

    354

    [/td]


    [td]

    485

    [/td]


    [td]

    811

    [/td]


    [/tr]


    [tr]


    [td]

    X CaliberSL R

    [/td]


    [td]

    169

    [/td]


    [td]

    193

    [/td]


    [td]

    225

    [/td]


    [td]

    269

    [/td]


    [td]

    337

    [/td]


    [td]

    464

    [/td]


    [td]

    759

    [/td]


    [/tr]


    [tr]


    [td]

    XcaliberWEDGE SPIN AS0037 R

    [/td]


    [td]

    0

    [/td]


    [td]

    0

    [/td]


    [td]

    256

    [/td]


    [td]

    317

    [/td]


    [td]

    388

    [/td]


    [td]

    481

    [/td]


    [td]

    767

    [/td]


    [/tr]


    [tr]


    [td]

    XCAL TOUR SL AS0031 R

    [/td]


    [td]

    163

    [/td]


    [td]

    183

    [/td]


    [td]

    210

    [/td]


    [td]

    243

    [/td]


    [td]

    289

    [/td]


    [td]

    356

    [/td]


    [td]

    558

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber5 S

    [/td]


    [td]

    172

    [/td]


    [td]

    194

    [/td]


    [td]

    226

    [/td]


    [td]

    283

    [/td]


    [td]

    348

    [/td]


    [td]

    490

    [/td]


    [td]

    800

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber6 S

    [/td]


    [td]

    177

    [/td]


    [td]

    203

    [/td]


    [td]

    240

    [/td]


    [td]

    289

    [/td]


    [td]

    356

    [/td]


    [td]

    489

    [/td]


    [td]

    800

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber6+ S

    [/td]


    [td]

    178

    [/td]


    [td]

    203

    [/td]


    [td]

    238

    [/td]


    [td]

    287

    [/td]


    [td]

    360

    [/td]


    [td]

    499

    [/td]


    [td]

    827

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber7 S

    [/td]


    [td]

    183

    [/td]


    [td]

    215

    [/td]


    [td]

    251

    [/td]


    [td]

    302

    [/td]


    [td]

    370

    [/td]


    [td]

    490

    [/td]


    [td]

    781

    [/td]


    [/tr]


    [tr]


    [td]

    Xcaliber7 Tour S

    [/td]


    [td]

    189

    [/td]


    [td]

    219

    [/td]


    [td]

    260

    [/td]


    [td]

    313

    [/td]


    [td]

    395

    [/td]


    [td]

    520

    [/td]


    [td]

    845

    [/td]


    [/tr]


    [tr]


    [td]

    X CaliberSL T S

    [/td]


    [td]

    183

    [/td]


    [td]

    206

    [/td]


    [td]

    241

    [/td]


    [td]

    294

    [/td]


    [td]

    368

    [/td]


    [td]

    500

    [/td]


    [td]

    831

    [/td]


    [/tr]


    [tr]


    [td]

    X CaliberSL S

    [/td]


    [td]

    181

    [/td]


    [td]

    207

    [/td]


    [td]

    240

    [/td]


    [td]

    287

    [/td]


    [td]

    358

    [/td]


    [td]

    487

    [/td]


    [td]

    784

    [/td]


    [/tr]


    [tr]


    [td]

    FUJIKURAGOLD S

    [/td]


    [td]

    190

    [/td]


    [td]

    193

    [/td]


    [td]

    216

    [/td]


    [td]

    255

    [/td]


    [td]

    306

    [/td]


    [td]

    426

    [/td]


    [td]

    680

    [/td]


    [/tr]


    [tr]


    [td]

    WISHON S2S WHITE LITE S

    [/td]


    [td]

    175

    [/td]


    [td]

    204

    [/td]


    [td]

    238

    [/td]


    [td]

    281

    [/td]


    [td]

    359

    [/td]


    [td]

    485

    [/td]


    [td]

    800

    [/td]


    [/tr]


    [tr]


    [td]

    XCAL TOUR SL S

    [/td]


    [td]

    170

    [/td]


    [td]

    192

    [/td]


    [td]

    222

    [/td]


    [td]

    268

    [/td]


    [td]

    334

    [/td]


    [td]

    439

    [/td]


    [td]

    704

    [/td]


    [/tr]


    [tr]


    [td]

    ALDILA TOUR BLUE ATX 55 S

    [/td]


    [td]

    170

    [/td]


    [td]

    188

    [/td]


    [td]

    218

    [/td]


    [td]

    259

    [/td]


    [td]

    319

    [/td]


    [td]

    410

    [/td]


    [td]

    596

    [/td]


    [/tr]


    [/TABLE]


    Thank you very much for the help!!!


    Rick

  • Re: Select multiple cells in a column


    Try this:


  • Re: Select multiple cells in a column


    Dear shknbk2,



    in the code, the statement: "For Each c In Selection", is "C" a variable or shorthand for active cell?


    i understand the logic of the code but i don't understand how VBA knows what "C" is.


    Thanks again for the help!!


    Rick

  • Re: Select multiple cells in a column


    This code probably should have had its variables declared at the beginning to make everything clear. If so, the Dim statement would have been something like "Dim c as Range".


    In "For Each" speak, the next variable (C in this case) is of the same type as the variable following "in". So, For Each c in Selection assigns "c" to be one of each of the things in Selection (or whatever other collection after "in") every time the loop is run. Since C wasn't declared at the beginning, it gets assigned to be whatever individual element makes up a Selection in this case. "C" can be any name you want to make up: C is just easier because it is short. I often use it to really refer to a cell, but the proper object is a Range.


    If I would have declared C to be an Integer, for example, then that line would have given me trouble when running the code because an integer isn't a Selection item.


    You can look up For Each loops on the web.

  • Re: Select multiple cells in a column


    shknbk2


    If i may bother you with another question?


    i am confused about the purpose of the two "set r =" statements. it seems to me that the second statement overwrites and nullifies the first statement. obviously not because the macro works.

  • Re: Select multiple cells in a column


    You are essentially right. The purpose for the Union is to join all the rows together. However, before I call Union, r has to be something. If not, r is "Nothing" and the Union line fails (found this out the hard way). So the first Set r makes r be the first row of data. Then the first Union call also sets r to be the union of r and the first row of data, which is the same thing. Since they are the same thing, this Union call just makes r the combination of the first row with the first row, which results in just the first row. However, from then on, the Union adds the other rows to it.


    The code could include an If test to see if c.Address <> Activecell.Address and then do the Union. However, that adds another line or two to the code, and it isn't needed as explained above.