Array copying to a filtered region
 

I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data are
written wrongly.

Any clue?

Array copying to a filtered region
 

Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Array copying to a filtered region
 

Vergel,

That would work if he was pasting into a row, but pasting it into a column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of the
array being pasted into each row. The below works for me, although I think
there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

Array copying to a filtered region
 

Doug,

Jason did say that arr is a 10x1 array of integers :-) .

I did miss the problem with fitlers.. Taking your approach, but using a 10x1
array:

Option Base 1
Public Sub test()
Dim arr(10, 1) As Integer
Dim i As Integer

For i = 1 To 10
arr(i, 1) = i
Next i

For i = 1 To 10
Cells(i, 1) = arr(i, 1)
Next i

End Sub




"Doug Glancy" wrote in message
...
Vergel,

That would work if he was pasting into a row, but pasting it into a column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of the
array being pasted into each row. The below works for me, although I
think there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----






----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Array copying to a filtered region
 

Doug,

Jason did say that arr is a 10x1 array of integers :-) .

I did miss the problem with fitlers.. Taking your approach, but using a 10x1
array:

Option Base 1
Public Sub test()
Dim arr(10, 1) As Integer
Dim i As Integer

For i = 1 To 10
arr(i, 1) = i
Next i

For i = 1 To 10
Cells(i, 1) = arr(i, 1)
Next i

End Sub




"Doug Glancy" wrote in message
...
Vergel,

That would work if he was pasting into a row, but pasting it into a column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of the
array being pasted into each row. The below works for me, although I
think there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----







----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Array copying to a filtered region
 

Vergel,

In my eagerness to show my smarts, I read right past the 10 x 1 array.
Together I think we've solved it though <g.

What's might be the reason for a 10 x 1 array anyways?

Doug

"Vergel Adriano" wrote in message
...
Doug,

Jason did say that arr is a 10x1 array of integers :-) .

I did miss the problem with fitlers.. Taking your approach, but using a
10x1
array:

Option Base 1
Public Sub test()
Dim arr(10, 1) As Integer
Dim i As Integer

For i = 1 To 10
arr(i, 1) = i
Next i

For i = 1 To 10
Cells(i, 1) = arr(i, 1)
Next i

End Sub




"Doug Glancy" wrote in message
...
Vergel,

That would work if he was pasting into a row, but pasting it into a
column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of
the
array being pasted into each row. The below works for me, although I
think there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----







----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

Array copying to a filtered region
 

Thanks Doug:

I had, of couse, tried your approach and I know that it works perfectly.
But, as you might know, writing individual data into each cell involves a
lot of traffic with worksheet and thus it is really really SLOW.
That's why I wanted to know any clue in writing ARRAY into worksheet.

Anyway, thanks a lot for your kind info.
Take care!

/Jason Yang


"Doug Glancy" wrote:

Vergel,

That would work if he was pasting into a row, but pasting it into a column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of the
array being pasted into each row. The below works for me, although I think
there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

Array copying to a filtered region
 

Depending on how complicated your filtring is, you might want to
consider simply turning off the filter, writing the array to the range,
and turning the filter back on.

Alan Beban

Jason Yang wrote:
Thanks Doug:

I had, of couse, tried your approach and I know that it works perfectly.
But, as you might know, writing individual data into each cell involves a
lot of traffic with worksheet and thus it is really really SLOW.
That's why I wanted to know any clue in writing ARRAY into worksheet.

Anyway, thanks a lot for your kind info.
Take care!

/Jason Yang


"Doug Glancy" wrote:

Vergel,

That would work if he was pasting into a row, but pasting it into a column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of the
array being pasted into each row. The below works for me, although I think
there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?



----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

Array copying to a filtered region
 

Jason,

Maybe this will be faster. Instead of a 10x1 integer array though, you'll
need to use a one dimensional array of strings. You'll also need to add the
Microsoft Forms 2.0 Object Library to your VBA project references to gain
access to the DataObject object.

Basically, put your data in an array of strings. Combine the array into one
string. Put it in the clipboard. Paste.


Option Base 1
Public Sub test()
Dim arr(10) As String
Dim i As Integer
Dim objDO As New DataObject
Dim strJoinedText As String

For i = 1 To 10
arr(i) = CStr(i)
Next i

strJoinedText = Join(arr, vbNewLine)
objDO.SetText strJoinedText
objDO.PutInClipboard
Range("A1").PasteSpecial xlPasteAll

End Sub




"Jason Yang" wrote:

Thanks Doug:

I had, of couse, tried your approach and I know that it works perfectly.
But, as you might know, writing individual data into each cell involves a
lot of traffic with worksheet and thus it is really really SLOW.
That's why I wanted to know any clue in writing ARRAY into worksheet.

Anyway, thanks a lot for your kind info.
Take care!

/Jason Yang


"Doug Glancy" wrote:

Vergel,

That would work if he was pasting into a row, but pasting it into a column
will just paste the first value of the array into each cell in the range.
Typically, when pasting an array into a column you need to say:

Range("A1:A10") = WorksheetFunction.Transpose(arr)

to accomplish what your implying, but in Jason's case there is also the
problem of the filter, which also results in just the first element of the
array being pasted into each row. The below works for me, although I think
there may be a better way:

Sub test()
Dim arr(1 To 10) As Long
Dim i As Long
For i = 1 To 10
arr(i) = i
Next i
For i = 1 To 10
Cells(i, 1) = i
Next i
End Sub

hth,

Doug

"Vergel Adriano" wrote in message
...
Jason,

Try this

Range("A1:A10") = arr


"Jason Yang" wrote in message
...
I have a serious problem in writing an array into a range.
The following code works perfectly when I do not use any Filter.

' arr is an 10x1 array of integers
' Need to write this array in the rows 1~10 in the first column
Range(Cells(1,1),Cells(10,1))=arr

But, if the AutoFilter is activated and some rows are hidden, the data
are
written wrongly.

Any clue?




----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----