Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

How can Array be used to copy from a horizontal single-row range (50 contiguous cells) to non-contiguous vertical ranges of 10cells each?

I'm trying to copy a static single-row horizontal range to five non-contiguous vertical static ranges.  They both have the same number of cells.

This is my code that doesn't work:
Range("$AV$76:$AV$85,$BA$76:$BA$85,$BG$76:$BG$85,$BM$76:$BM$85,$BS$76:$BS$85").value = Range("$AN$5:$CK$5").value

Thanks, --Andres
2 Solutions
Rory ArchibaldCommented:
You can't do that. You will have to do it in 5 separate chunks.
Saqib Husain, SyedEngineerCommented:
Since the gap between the first two ranges is different from the gap between the remaining ranges you will have to use something like

Sub transnoncont()
Range("$AV$76:$AV$85").Value = WorksheetFunction.Transpose(Range("$AN$5:$AW$5"))
Range("$BA$76:$BA$85").Value = WorksheetFunction.Transpose(Range("$AX$5:$BG$5"))
Range("$BG$76:$BG$85").Value = WorksheetFunction.Transpose(Range("$BH$5:$BQ$5"))
Range("$BM$76:$BM$85").Value = WorksheetFunction.Transpose(Range("$BR$5:$CA$5"))
Range("$BS$76:$BS$85").Value = WorksheetFunction.Transpose(Range("$CB$5:$CK$5"))
End Sub
AndresHernandoAuthor Commented:
Thanks for the solution.  Works great.  --Andres

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now