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
3
305 Views
Last Modified: 2012-05-11
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
Comment
Question by:AndresHernando
3 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
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

by:
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

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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now