Solved

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

Posted on 2011-03-08
305 Views
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
0
Question by:AndresHernando

LVL 85

Assisted Solution

Rory Archibald earned 50 total points
ID: 35068906
You can't do that. You will have to do it in 5 separate chunks.
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 450 total points
ID: 35068965
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
0

Author Closing Comment

ID: 35069149
Thanks for the solution.  Works great.  --Andres
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…