Repeatedly Filling a Range of Cells with Copied Cells

You can copy a range of cells and fill another range with those cells, copying the data and the cell type with the FillRange method. For example, if you have a 2x2 range, you can repeat (fill) down the next 5 groups of 2x2 vertically.

The parameters for the FillRange method are:

starting cell row and column index
number of rows and columns in the range to copy
number of either rows (if left or right) or columns (if up or down) to copy that range (not the number of times to repeat the entire range; the number of either rows or columns)

Ranges of Cells Filled

Using Code

Add data to the cells.
Set the FillRange method.

Example

For example, using this code, you would accomplish the results shown in the preceeding figure.

C#	Copy Code
// Define the text to repeat. fpSpread1.ActiveSheet.Cells[0, 0].Text = "A1-text"; fpSpread1.ActiveSheet.Cells[0, 1].Text = "A2-text"; fpSpread1.ActiveSheet.Cells[1, 0].Text = "B1-text"; fpSpread1.ActiveSheet.Cells[1, 1].Text = "B2-text"; fpSpread1.ActiveSheet.Cells[0, 0].BackColor = Color.Cyan; fpSpread1.ActiveSheet.Cells[0, 0].ForeColor = Color.DarkBlue; fpSpread1.ActiveSheet.Cells[0, 1].BackColor = Color.Coral; fpSpread1.ActiveSheet.Cells[0, 1].ForeColor = Color.DarkRed; // Fill 3 more columns to the right with the two columns' contents fpSpread1.ActiveSheet.FillRange(0, 1, 2, 1, 3, FillDirection.Right); // Fill 4 more rows down with the contents of the square // of 2 rows and 2 columns fpSpread1.ActiveSheet.FillRange(0, 0, 2, 2, 4, FillDirection.Down);

Copy Code

// Define the text to repeat.
fpSpread1.ActiveSheet.Cells[0, 0].Text = "A1-text";
fpSpread1.ActiveSheet.Cells[0, 1].Text = "A2-text";
fpSpread1.ActiveSheet.Cells[1, 0].Text = "B1-text";
fpSpread1.ActiveSheet.Cells[1, 1].Text = "B2-text";
  
fpSpread1.ActiveSheet.Cells[0, 0].BackColor = Color.Cyan;
fpSpread1.ActiveSheet.Cells[0, 0].ForeColor = Color.DarkBlue;
fpSpread1.ActiveSheet.Cells[0, 1].BackColor = Color.Coral;
fpSpread1.ActiveSheet.Cells[0, 1].ForeColor = Color.DarkRed;
  
// Fill 3 more columns to the right with the two columns' contents
fpSpread1.ActiveSheet.FillRange(0, 1, 2, 1, 3, FillDirection.Right);
// Fill 4 more rows down with the contents of the square
// of 2 rows and 2 columns
fpSpread1.ActiveSheet.FillRange(0, 0, 2, 2, 4, FillDirection.Down);

VB	Copy Code
' Define the text to repeat. FpSpread1.ActiveSheet.Cells(0, 0).Text = "A1-text" FpSpread1.ActiveSheet.Cells(0, 1).Text = "A2-text" FpSpread1.ActiveSheet.Cells(1, 0).Text = "B1-text" FpSpread1.ActiveSheet.Cells(1, 1).Text = "B2-text" FpSpread1.ActiveSheet.Cells(0, 0).BackColor = Color.Cyan FpSpread1.ActiveSheet.Cells(0, 0).ForeColor = Color.DarkBlue FpSpread1.ActiveSheet.Cells(0, 1).BackColor = Color.Coral FpSpread1.ActiveSheet.Cells(0, 1).ForeColor = Color.DarkRed ' Fill 3 more columns to the right with the two columns' contents FpSpread1.ActiveSheet.FillRange(0, 1, 2, 1, 3, FillDirection.Right) ' Fill 4 more rows down with the contents of the square ' of 2 rows and 2 columns FpSpread1.ActiveSheet.FillRange(0, 0, 2, 2, 4, FillDirection.Down)

Copy Code

' Define the text to repeat.
FpSpread1.ActiveSheet.Cells(0, 0).Text = "A1-text"
FpSpread1.ActiveSheet.Cells(0, 1).Text = "A2-text"
FpSpread1.ActiveSheet.Cells(1, 0).Text = "B1-text"
FpSpread1.ActiveSheet.Cells(1, 1).Text = "B2-text"
  
FpSpread1.ActiveSheet.Cells(0, 0).BackColor = Color.Cyan
FpSpread1.ActiveSheet.Cells(0, 0).ForeColor = Color.DarkBlue
FpSpread1.ActiveSheet.Cells(0, 1).BackColor = Color.Coral
FpSpread1.ActiveSheet.Cells(0, 1).ForeColor = Color.DarkRed
  
' Fill 3 more columns to the right with the two columns' contents
FpSpread1.ActiveSheet.FillRange(0, 1, 2, 1, 3, FillDirection.Right)
' Fill 4 more rows down with the contents of the square
' of 2 rows and 2 columns
FpSpread1.ActiveSheet.FillRange(0, 0, 2, 2, 4, FillDirection.Down)