Hi Ashish,
You can build an array of array and stuff the data into the Worksheet
in one instruction.
The values 2,4,8 and 2,8,11 are values I cooked up to so you could
see a working example.
You'd replace these values with values coming from your data source -
like a DAO ResultSet.
Note that you might run into performance problems if you're inserting
20000 rows at a time - because VBA will be keeping all that data in
memory.
So you might find it better to burst the data into the sheet 100 rows
at a time.
YMMV
Sub test()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
Dim ar() As Variant
s.Activate
ReDim Preserve ar(1 To 1)
ar(1) = Array(2, 4, 8)
ReDim Preserve ar(1 To 2)
ar(2) = Array(2, 8, 11)
s.Range("A1:C2").Value = Application.Transpose(Application.Transpose
(ar))
End Sub
Sub test2()
Dim b As Workbook
Dim s As Worksheet
Set b = ThisWorkbook
Set s = b.Sheets("Sheet1")
Dim ar(1 To 2) As Variant
s.Activate
ar(1) = Array(2, 42, 8)
ar(2) = Array(2, 84, 11)
s.Range("A1:C2").Value = Application.Transpose(Application.Transpose
(ar))
End Sub
www.gearyandpowell.com