Range to Array Resize
Range to Array Resize
Range to Array Resize
Range to Array Resize
Range to Array Resize
Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize
Range to Array Resize Range to Array Resize
Range to Array Resize
Go Back  Xtreme Visual Basic Talk > > > > Range to Array Resize


Reply
 
Thread Tools Display Modes
  #1  
Old 01-15-2017, 11:18 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default Range to Array Resize


I have a range with 199 rows and 15 columns and I use "SpecialCells(xlCellTypeVisible)". If I copy this range to another range, it results in a new range with 8 columns which is what I want. I was copy/pasting this range to another worksheet but want to put the range values into an array "x1", then put the array back to another defined range (199 rows, 8 columns) on another worksheet. The result is that I am passing the first column only to the new range so I get 8 times the first column values. I've tried using "Resize" for the range. Seems like the range values don't get passed to the array correctly. My workaround has been to copy the visible column only range to another range, then put this into the x1 array, then set the other range equal to x1, and it works fine. However, I'd like to determine how to skip the step of copying the visible range to another range. Here is the code:
Code:
Dim rng1 As Range, rng2 As Range, x1 As Variant
'wsA and wsB are worksheets

Set rng1 = wsB.Range("E2:S200").SpecialCells(xlCellTypeVisible)
    rng1.Copy wsB.Range("U2")   'want to eliminate this step
Set rng1 = wsB.Range("U2:AB200")
    x1 = rng1
    
Set rng2 = wsA.Range("J4:Q202")
    rng2 = x1
Reply With Quote
  #2  
Old 01-16-2017, 08:42 PM
Kluz's Avatar
KluzRange to Array Resize Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

The problem with the SpecialCells(xlCellTypeVisible) object is that it contains multiple Areas and with the .Value = .Value methodology if you don't specify which area it will default to .Area(1).
You can loop through .Areas.Count, get the .Columns.Count from each area, define (resize) the destination range and then do a .Value = .Value then loop to the next area and repeat. If your hidden columns are always the same ones you won't need the .Areas.Count to determine the loop count nor .Columns.Count to resize the destination range.
The number of areas will determine if it's faster or slower then the copy/paste method or are you just wanting to avoid using the clipboard?
__________________
No the other right mouse click
Reply With Quote
  #3  
Old 01-16-2017, 11:17 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default

Kluz, thanks.
The desired area is always the same - 199 rows and 8 columns. I have tried resizing rng1, but my syntax must be wrong.

Code:
Set rng1 = rng1.Resize(199,8)
I get an error '1004'. For this size range the copy method is probably just as fast.
Reply With Quote
  #4  
Old 01-17-2017, 08:24 PM
Kluz's Avatar
KluzRange to Array Resize Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,445
Default

You need to resize the destination range to match the column count of the first area in the source range: Column C is hidden = .Resize(199, 2)
Then loop and resize the destination for the second destination range: rng1.Offset(0, 2).Resize(199, x) where x = the column count of your second area of contiguous source range columns: Area(2)
Loop to the .Areas.Count adjusting the column's count and the offset within the loop
__________________
No the other right mouse click
Reply With Quote
  #5  
Old 01-19-2017, 12:03 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default

Kluz, thanks.
Still a bit over my head but I have spent some time trying to get my arms around the Resize concept. Also tried a Union of ranges, which I dumped into an array but then when I copied the array to a range I got only rng1 printed out three times
Code:
rng1 = Range("A1:A10")
rng2 = Range("C1:C10")
rng3 = Range("E1:E10")

rng4 = Union(rng1, rng2, rng3)
x = rng4
Range("G1:I10") = x
Reply With Quote
  #6  
Old 01-19-2017, 07:54 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Thumbs up

I've made some progress and now better understand the Resize function. Here is some code that puts three columns into a variant array, which I can then print out to the page in columns G:I.
Code:
Sub AddArr()
Dim Data() As Variant
Dim r As Integer

Sheets("Sheet1").Select
Erase Data()
For r = 1 To 199
   ReDim Preserve Data(1 To 199, 1 To 3)
   Data(r, 1) = Range("A" & r).Value 'fills the array with col A
   Data(r, 2) = Range("C" & r).Value 'fills the array with col C
   Data(r, 3) = Range("E" & r).Value 'fills the array with col E
Next r
Worksheets("Sheet1").Range("G1:I199") = Data()
I put a timer on this and it is approximately twice as fast as copy/paste. I would like to whittle this down further. Something like:

Code:
For r = 1 to 199
For c = 1 to 3
ReDim Preserve Data(1 To 199, 1 To 3)
Data(r, c) = Range(.....
Next c
Next r
Reply With Quote
  #7  
Old 01-20-2017, 04:19 PM
cacody cacody is offline
Junior Contributor
 
Join Date: Apr 2011
Location: Scottsdale AZ
Posts: 323
Default

Found and modified this sub:
Code:
Sub ToArray()
Dim arr() As Variant, R As Long, nr As Long
Dim ar As Range, C As Range, cnum As Long, rnum As Long
Dim col As Range

Set Rng = Range("A1:H199").SpecialCells(xlCellTypeVisible)
'    nr = Rng.Areas(1).Rows.Count
'    ReDim arr(1 To nr, 1 To Rng.Cells.Count / nr)
nr = 199
ReDim arr(1 To nr, 1 To 5)
cnum = 0
For Each ar In Rng.Areas
    For Each col In ar.Columns
        cnum = cnum + 1
        rnum = 1
        For Each C In col.Cells
            arr(rnum, cnum) = C.Value
            rnum = rnum + 1
        Next C
    Next col
Next ar
Range("J1:N199") = arr
End Sub
This is what I was looking for. I hard coded "nr" and the ReDim of arr but left in code (commented out) to account for ranges of any size. Also note that use of the SpecialCells requires hiding unwanted columns.
Reply With Quote
Reply

Tags
range, array, set, rng1, values, rng2, columns, column, visible, step, worksheet, rows, resize, copy, determine, skip, wsb.rangeu2ab200, wsa.rangej4q202, equal, fine, copying, code, wsb, wsa, worksheets


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Range to Array Resize
Range to Array Resize
Range to Array Resize Range to Array Resize
Range to Array Resize
Range to Array Resize
Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize Range to Array Resize
Range to Array Resize
Range to Array Resize
 
Range to Array Resize
Range to Array Resize
 
-->