Manipulating a Range

P

Pablo

I have a range that reads right to left (column
A2:F1517). I would like to take the range and put the
values into one single column, preferably on another
worksheet.
 
F

Frank Kabel

Hi
one way:

dim swks as worksheet
dim twks as workshee
Dim rng as range
dim cell as range
dim row_index as long
application.screenupdating=false
set swks=activesheet
set twks as worksheet("sheet2")
set rng=swks.range("A1:F1000")
row_index=1
for each cell in rng
twks.cells(row_index,1).valuecell.value
row_index=row_index+1
next
application.screenupdating=true
 
P

Pablo

Thanks. When I run the routine I getting a compile error
at set twks as worksheet("sheet2")

Sub Saxon999()

Dim swks As Worksheet
Dim twks As Worksheet
Dim rng As Range
Dim cell As Range
Dim row_index As Long
Application.ScreenUpdating = False
Set swks = ActiveSheet
set twks as worksheet("sheet2")
Set rng = swks.Range("A2:F1518")
row_index = 1
For Each cell In rng
twks.Cells(row_index, 1).valuecell.Value
row_index = row_index + 1
Next
Application.ScreenUpdating = True

End Sub
 
A

Alan Beban

Pablo said:
I have a range that reads right to left (column
A2:F1517). I would like to take the range and put the
values into one single column, preferably on another
worksheet.
Click to expand...
I don't know what you mean by "reads right to left", but if the
functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will return the elements from the first sheet,
Range("A2:F1517"), to the second sheet, Range("A1:A9096"),
with the value from Cell B2 on the 1st sheet in Cell A2 of the 2nd
sheet. If instead you want the value from A3 of the 1st sheet in Cell
A2 of the 2nd sheet, change the last line to
rng2.Value = ArrayReshape(rng1, n, 1, "c").

Sub a()
Dim rng1 As Range, rng2 As Range, n As Long
Set rng1 = Sheets(1).Range("A2:F1517")
n = rng1.Count
Set rng2 = Sheets(2).Range("a1:a" & n)
rng2.Value = ArrayReshape(rng1, n, 1)
End Sub

Alan Beban
 
A

Alan Beban

That's one of the risks when a responder is in too much of a hurry to
take the time to test code before posting it; it should be
Set twks = Worksheets("Sheet2"). There's also a similar "haste makes
waste" problem at the line

twks.Cells(row_index, 1).valuecell.Value which should be
twks.Cells(row_index, 1).value = cell.Value

Alan Beban
 
Top