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, etc. For example, if you have a 2x2 range, you can repeat (fill) down the next 5 groups of 2x2 vertically.

Use the FillRange method. The parameters for this 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

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

Example

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)

Return to the overview at Placing and Retrieving Data.