Writing array into worksheet using macro

[ushzz]

Hi,

Is it possible to write a partial array into the worksheet range at once?

To be more clear, i don't want to write the zero values in my array into the file. Loops are taking so long. Thanks in advance.

 

[xld]

Why not just drop the whole array in and then replace all 0 with blank?

 

[ushzz]

Hello xld,

Thanks for the reply.But Neither do i want to write the blanks. Only those new data in the array must be written into the file.

 

[Hui]

Ushzz


I Don't believe you can do what your proposing


You can write part of an array to a range, but not skipping array elements which have a certain value

 

[xld]

How about this

[pre]

Sub DumpArray()
Dim ary As Variant
Dim cntRow As Long
Dim cntCol As Long

ary = [{1,"a","1a";2,"b","2b";3,0,"3c";0,"d",0;5,"e","5e"}]
cntRow = UBound(ary, 1) - LBound(ary, 1) + 1
cntCol = UBound(ary, 2) - LBound(ary, 2) + 1
With Range("A1").Resize(cntRow, cntCol)

.Value = ary

.Replace What:="0", Replacement:="", LookAt:=xlWhole
.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End With

End Sub

[/pre]

 

[ushzz]

Hello Hui,


Can you tell me how to write part of an array into a range?

Thank you.

 

[ushzz]

Please Help!!!

 

[Hui]

Ushzz


Have a look below:

[pre]

Sub Write_Partial_Array()

Dim myArray(1 To 10, 1 To 10) As Variant
Dim i As Integer, j As Integer

'Setup A(10,10) array
For i = 1 To 10
For j = 1 To 10
myArray(i, j) = i * j
Next j
Next i

'Write the first 3 Rows & 6 Columns to A1
Worksheets("Sheet1").Range("A1:F3") = myArray

'or Use Resize

'Write the first 8 Rows & 2 Columns to H1
Worksheets("Sheet1").Range("H1").Resize(8, 2) = myArray

End Sub

[/pre]

 

[NARAYANK991]

Hi ,


The simplest way to write a partial / entire array to a range is to :


1. Write the entire range to the array


2. Do whatever processing is required on the array elements


3. Write the entire array back to the range


This is assuming that your range has no formulae in it.


Narayan

 

[ushzz]

Thank you so much!