changing the type of array from variant to integer 1

[bartrein]

is this possible without a loop?

let's say i wanted to return the content of :

vaData= range("a1:a65000").value

to another range as integers (loosing decimals).

many thanks

 

[SkipVought]

Hi,

Do you REALLY have 65000 rows of data?

Is ther a reason for not using a loop?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

How about

Range("B1:B65000").Formula="=Round(A1,0)"

 

[macropod]

Hi Bartrein,

Try somehing along the lines of:

Sub GetIntegers()
With Range("B1:B100")
  .FormulaArray = "=INT(RC[-1]:R[99]C[-1])"
  .Value = .Value
End With
End Sub

The above code inserts into B1:B100 the integer components of A1:A100.

Note that FormulaArray requires the R1C1 reference format.

Cheers

[MS MVP - Word]

 

[DougAJ4]

Asuming you want to avoid a loop to make it fast, this is pretty fast, even though it's got a loop:

Sub copyint()
Dim vaData As Variant, lData(1 To 65000, 1 To 1) As Long
Dim i As Long

vaData = Range("a1:a65000").Value2
For i = 1 To 65000
lData(i, 1) = vaData(i, 1)
Next i

Range("b1:b65000").Value2 = lData

End Sub

 

[bartrein]

Many thanks for your answers.

I have asked this question because i am trying to workout the quickest method or the best practise of extracting data from one place to another (In majority of my macros there is always that part where i have to open a spreadsheet or get all the values from column C and paste them into new sheet, delete duplicates or records i don't want etc). I would like to write a piece of reusable code that would adopt that method and make my life a bit easier.

I have read and tested (using the timer) that loops take a bit longer to retrieve/return data that's why i thought i would try to avoid them.

Up to date i have been using variant arrays but because of different data formats i would like to have a bigger control over what goes out of a variant array.


Can i ask you guys for some advice on how do you do approach these things?

Say you have a large spreadsheet tens of thousands of records, :
- one column (A) stores text codes (could be all numeric starting with 0),
-another stores date values (B)
-another (C) numbers.
Let's say you want to quickly grab all that data and return into another sheet. Making sure you don't loose 0's from text codes, return dates in yyyymmdd format, round numbers 2 second decimals etc.

How would you :
1. Grab that data
- variant array vaData=Range("a1:a65000")
- Dim sText (1 to 65000) as String, Loop
- Range("a1:a65000").Copy
- other
- does it really matter ?

2. Return data
- from variant array
- Array.Transpose
- Range(...)Paste.Special Values/Formats

3. Modify data
- loop within VBA
- functions on the spreadsheet (FormulaR1C1) - i've heard somewhere that this should be quickest as worksheet functions are written in c++ don't need to be compiled, but somehow it works slower for me (yes with calc=xlmanual)

Sorry for this long post and thank you for sharing your experience

bartrein

 

[macropod]

Hi bartrein,

Since you basically want to replicate the data from sheet 1 on sheet 2, the simplest method is to copy & paste the entire range, then round (and perhaps re-format) the data in column C. The copy/paste operation can preserve all the formatting attributes if you need them. As to whether a loop would be faster than the FormulaArray approach I showed (which can traverse worksheets), combined with any reformatting you require, I don't know.

In terms of speed, the most important thing is to use range objects instead of selections.

Also, if you're processing many worksheets in one go, you'll likely find Excel slowing down after the first few. I've found that processing the data in memory arrays before writing the results to a worksheet can avoid that performace hit.

Cheers

[MS MVP - Word]

 

[SkipVought]

In majority of my macros there is always that part where i have to open a spreadsheet or get all the values from column C and paste them into new sheet, delete duplicates or records i don't want etc

There are better ways to do what you describe. One way is MS Query. faq68-5829.

Select Int[i]([Col A Field])[/i] 
From [i][YourSheet[/i]$]
Where [i]SomeFilterExpression[/i]

1. Grab that data
- variant array vaData=Range("a1:a65000")
- Dim sText (1 to 65000) as String, Loop
- Range("a1:a65000").Copy
- other
- does it really matter ?

2. Return data
- from variant array
- Array.Transpose
- Range(...)Paste.Special Values/Formats

These two steps cannot work together! You are taking Range("a1:a65000") and Array.Transpose. Excel only has 256 columns. TILT!

Suppose you explain the business case for this requirement.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}