PDA

View Full Version : Range that increments column number



darylb
01-21-2009, 08:19 AM
I'm writing a macro to select and copy certain blocks of data arranged vertically in one sheet and paste horiztonally in another sheet. I made a FOR loop do this cell by cell but it is very slow so I want to use ranges however I cannot figure out how to refer to a range using index numbers so that I can increment those variables.

Currently I have:


For k = 1 To 26
For c = 2 To 10
For y = 1 To 90
Sheets(n).Select
Cells(r, c).Select
Selection.Copy
Sheets(o).Select
Cells(i, j).Select
ActiveSheet.Paste
i = i + 1
r = r + 1
Next y
r = r - 90
i = 1
j = j + 1
Next c
r = r + 285
Next k

I could replace the two inner loops with range references if these is a way I can refer to the column number of where i'm pasting as a variable which I can increment.

darylb
01-21-2009, 08:24 AM
I dont need my code fixed so much as I need to know how to refer to a range using index numbers (variables) for the row & column. In addition, some tipts on the most effecient way to copy and paste would be useful too

lenze
01-21-2009, 09:12 AM
Maybe this will help?
On Sheet1, enter some values in a column (Say A1:A3). Now record a macro as such
Select the range and copy.
Next Go to another sheet and do a Paste Special, using Transpose. This should give you some ideas.
My experment looks like this


Sub Macro3()
'
' Macro3 Macro
' Macro recorded 1/20/2009 by lenze
'
'
Range("A1:A3").Select
Selection.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub


lenze

Bob Phillips
01-21-2009, 10:07 AM
I dont need my code fixed so much as I need to know how to refer to a range using index numbers (variables) for the row & column. In addition, some tipts on the most effecient way to copy and paste would be useful too

You do!

You can speed it up by cutting out the selects



For k = 1 To 26

For c = 2 To 10

For y = 1 To 90

Sheets(n).Cells(r, c).Copy Sheets(o).Cells(i, j)
i = i + 1
r = r + 1
Next y

r = r - 90
i = 1
j = j + 1
Next c

r = r + 285
Next k


but if you tell us what you are doing, I am sure we can speed it up more, why is there 3 levels of loops, what are k, c, y ,n, o etc.