Excel VBA array padding

sub autofill().

dim i as integer, namename = array("Table 1", "Table 2", "TABLE 3", "TABLE 4", "TABLE 5", "TABLE 6", "TABLE 7")

for i = 0 to ubound(name)windows(name(i) &".xls").activate"a1:

be183").copy workbooks("Summary. xls").

worksheets(name(i)).range("a1")

windows("Summary. xls").activatenext i

end sub

The prerequisite is that all workbooks must be opened first.

1 Case Description.

When you want to process a large amount of data in Excel, you can use a loop to read the data from each cell, process it, and then write it back to the cell dry grid area. This is slower than processing data in an array. Therefore, if there is a large amount of data to be processed, you can save the data to the array first, and then fill the data of the array into the cell range after processing.

This example shows how to populate data from a two-dimensional array into a worksheet. Bury stupidity.

2 Turn off the fluid like a key technique.

In Excel worksheet, a worksheet is a two-dimensional structure consisting of rows and columns. This feature is similar to that of 2D arrays, so it is easy to convert between a range of worksheet cells and a 2D array. You can assign a range of cells to a two-dimensional array with the following statement:

myarr = range(cells(1, 1), cells(5, 5))

Conversely, you can quickly assign the value in the 2D array to a cell range, such as the following statement to assign the value in the 2D array myarr to the cell range rng:

arr3.

1) Create a new Excel workbook and press the shortcut key "Alt+F11" to enter the VBE environment.

2) Click the menu "Insert Module" command to insert a module into the project.

3) Write the following in the module:

option base 1

The sub array fills the cell range ().

dim i as long, j as long

dim col as long, row as long

dim arr() as long

row = :="Enter the number of rows:",type:=2)

col = :="Enter the number of columns:",type:=2)

redim arr(row, col)

for i = 1 to row

for j = 1 to col

arr(i, j) =i - 1) *col + j

nextnext

set rng = sheets(1).range(cells(1, 1), cells(row, col))

arrend sub

4) Return to the excel operating environment, add a button to the worksheet, set the prompt text as "fill data", and specify the macro of the button as "array to fill the cell range".