MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Want an EXCEL RECORDSET based on an EXCEL RANGE - The plot thickens!


Posted by bill on November 16, 2001 8:25 AM

Hello.

Using RECORDSETS in ACCESS, you can quickly copy a RECORDSET to an ARRAY.

I assume you can do the same in EXCEL.

So, if you were to define an EXCEL RECORDSET to a workbook RANGE, it seems plausable to use the RECORDSET METHOD:

.GetRows

Doing so would yeild something like:

Dim RS As Recordset
Dim lngLastRow As Integer
Dim lngRows as Integer
Dim varData as Variant

lngLastRow = Range("A65536").End(xlUp).Row
Set RS = Excel.Range("A2:B" & lngLastRow)
varData = RS.GetRows(lngLastRow) ' You CAN specify a number greater than the actual RANGE. GetRows will not crap out!


I get a TYPE MISMATCH using the Set Command.


Posted by Dank on November 16, 2001 8:48 AM

I get a TYPE MISMATCH using the Set Command.


If you want to copy an entire range to an array you simply say:-

vArray = Range("A1:H68")

In this case vArray would be a 2d array which you can then manipulate. To assign it back to a range do this:-

Range("A1:H68") = vArray


This is the quickest way to work with large amounts of data in Excel.

Regards,
Daniel.

Posted by bill on November 16, 2001 9:08 AM

COOOOOOOOLLLL!!!!!!

If you want to copy an entire range to an array you simply say:- vArray = Range("A1:H68") In this case vArray would be a 2d array which you can then manipulate. To assign it back to a range do this:- Range("A1:H68") = vArray This is the quickest way to work with large amounts of data in Excel. Regards, Daniel.

Posted by bill on November 16, 2001 9:48 AM

Hold on a sec...

Not...


Dim lngLastRow As Long
Dim astrOrgs() As Variant

lngLastRow = Range("GenLabels!A65536").End(xlUp).Row
astrOrgs = Range("GenLabels!A2:B" & lngLastRow)

I get an error: Can't assign to an array

Anything wrong here??

COOOOOOOOLLLL!!!!!!

Posted by bill on November 16, 2001 10:30 AM

Re: Hold on a sec...Revised

This instead:

Sub ThisArray()

Dim TheArray As Variant

TheArray = Range("A1:B10").Value

Range("C1:D10") = TheArray

End Sub

-------------------------------------

Seems you must use the VALUE property in the ARRAY declaration...