Filling excel entire row/column instead of single cell from an array

[shitij]

Hi,
I have a 2-dimensional variant array. If I want to fill up the excel worksheet, the way to fill it up using VB program is:-
If rCount <> 0 Then
For iRow = 1 To rCount
For iColumn = 1 To cCount
ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
Next
Next
End If

Here, ws = Excel worksheet object
arr() = two-dimensional array of type variant from where values need to be filled
rCount = Number of Rows
cCount = Number of Columns
iRow = counter for rows and iColumn = counter for columns

My problem is that loop within a loop does take lot of time, if rows and columns are around 50 or so. Is there anyway to avoid loop so as to decrease loop within loop time.

Thanks in advance

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[shitij]

Thanks for the appropriate reply. That does really helped me. In continuation to above reply, can you advise me the way to have cells of whole excel file in an array in a single shot instead of reading it in a loof of reading cells individually. Your words of little help can work wonders for me.
Once again, thanks for the suitable reply.

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi,

This one works for me:

Sub test()
Dim arr(1 To 20, 1 To 10)
For j = 1 To 10
For i = 1 To 20
arr(i, j) = i + j
Next i
Next j
Range("a1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

Regards,
KL



"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have a 2-dimensional variant array. If I want to fill up the excel
> worksheet, the way to fill it up using VB program is:-
> If rCount <> 0 Then
> For iRow = 1 To rCount
> For iColumn = 1 To cCount
> ws.cells(iRow, iColumn).Value = arr(iRow, iColumn)
> Next
> Next
> End If
>
> Here, ws = Excel worksheet object
> arr() = two-dimensional array of type variant from where
> values need to be filled
> rCount = Number of Rows
> cCount = Number of Columns
> iRow = counter for rows and iColumn = counter for columns
>
> My problem is that loop within a loop does take lot of time, if rows
> and columns are around 50 or so. Is there anyway to avoid loop so as to
> decrease loop within loop time.
>
> Thanks in advance
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>

[KL]

Hi shitij,

Do you really mean "cells of whole excel file" or maybe the cells of the
whole used range on a sheet of that file? If it is the latter then it is as
easy as the following:

Dim Arr as Variant
arr=ActiveSheet.UsedRange.Value

Regards,
KL

"shitij" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the appropriate reply. That does really helped me. In
> continuation to above reply, can you advise me the way to have cells of
> whole excel file in an array in a single shot instead of reading it in a
> loof of reading cells individually. Your words of little help can work
> wonders for me.
> Once again, thanks for the suitable reply.
>
>
> --
> shitij
> ------------------------------------------------------------------------
> shitij's Profile:
> http://www.excelforum.com/member.php...o&userid=25145
> View this thread: http://www.excelforum.com/showthread...hreadid=386391
>