0

I need to select multiple ranges.

This is my code to select the first range, but now I need to edit this line to add the second range.

Range(ActiveCell.Offset(-1, -8), ActiveCell.Offset(-1, -2)).Select

Range("A:G,T:W") is what I'm trying to grab dynamically.

Update: VincentG gave me code 

Intersect(ActiveCell.EntireRow, Range("A:G,T:W")).Copy

I've copied the range, but now I need to paste it in the row below, same columns. I tried to use the same code 

Intersect(ActiveCell.EntireRow, Range("A:G,T:W")).PasteSpecial (xlPasteValues)

to paste but it gives me the error

"This action won't work on multiple selections"

Community
  • 1
  • 1
Belair58
  • 3
  • 3
  • 1
    What's dynamic about `Range("A:G,T:W")`? What are you actually trying to achieve, in plain English please? – SJR Jan 09 '20 at 15:53
  • You probably don't have to use `Select`, this [answer](https://stackoverflow.com/a/10717999/4717755) can provide some guidance. – PeterT Jan 09 '20 at 15:53
  • Are you trying something like: `Range(Replace("A?:G?,T?:W?", "?", ActiveCell.Row)).Select` ??. However, I agree with the above. Most likely you can avoid the `.Select`. another thing would be to avoid implicit sheet references. – JvdV Jan 09 '20 at 16:30
  • Are you trying to select cells in columns A:G and T:W in the same row as ActiveCell? – JohnyL Jan 09 '20 at 18:19
  • SJR, I'm looking to copy and paste two different ranges using active cell in a loop. So dynamically means for each time a particular cell has criteria in it, this should run. – Belair58 Jan 09 '20 at 19:20
  • JohnyL, no, one row up from the activecell – Belair58 Jan 09 '20 at 19:21

2 Answers2

1

Your code is syntactically correct, but I suspect you aren't getting the expected result. Further guessing: I guess you want to select A:G and T:W on the current row. If that guess is correct, then try this:

    Union( _
        Range(Cells(ActiveCell.Row, "A"), Cells(ActiveCell.Row, "G")), _
        Range(Cells(ActiveCell.Row, "T"), Cells(ActiveCell.Row, "W")) _
    ).Select
Sam
  • 5,424
  • 1
  • 18
  • 33
  • 2
    Your code will select A:H not A:G. Just a typo I'm sure. – Eric Nolan Jan 09 '20 at 16:09
  • 2
    `Intersect(ActiveCell.EntireRow, Range("A:G,T:W")).Select` – Vincent G Jan 09 '20 at 16:16
  • I used to select what I needed now I'm attempting to use it to Paste and I'm getting an error "This can't be done on a multiple range selection" Intersect(ActiveCell.EntireRow, Range("A:G,T:W")).Copy Intersect(ActiveCell.EntireRow, Range("A:G,T:W")).Offset(1, 0).PasteSpecial xlPasteAll – Belair58 Jan 09 '20 at 19:17
0

For anyone else looking at this post:

I ended up copying the one section, pasting it where I needed it, then using activecell.offset, I chose the other range, copied and pasted. I never did find a way to copy and paste multiple ranges.

Belair58
  • 3
  • 3