Filling a Set Number of Cells

When Dennis is developing a worksheet, he knows he can use the fill handle to drag downward to fill cells with a formula (the formula in the cell that he's dragging down). He wonders, though, if there is a fast way to fill downwards a specific number of cells, such as 50 or 100 cells.

Short of using a macro, there are two very easy ways to accomplish this task. Here is the first approach:

1. Select the cell you want to fill downward. For instance, if you want to fill cell C2 downward, select that cell.
2. Press Ctrl+C. The contents of the cell are copied to the Clipboard.
3. In the Name box (just above the row numbers and to the right of the column letters) enter the target range, such as C3:C50. When you press Enter, the range of cells is selected.
4. Press Enter another time. The selected range is filled with the contents of the Clipboard.

An even quicker way that bypasses the Clipboard is to follow these steps:

1. Select cell C2.
2. In the Name box, enter the entire target range, including the currently selected cell. So, you would enter C2:C50. When you press Enter, the entire range is selected.
3. Press Ctrl+D. The formula in the first cell of the range (C2) is copied to the other cells in the range.

If you prefer to go a macro-based route, the following is one approach:

Sub FillRows()
    Dim Nrows As Variant

    Nrows = Application.InputBox("Fill how many rows?", Type:=1)
    If Nrows Then
        If Nrows < 0 And Abs(Nrows) >= ActiveCell.Row Then
            Nrows = -ActiveCell.Row + 1
        End If
        ActiveSheet.Range(ActiveCell.Address).AutoFill _
          Destination:=Range(Cells(ActiveCell.Row, _
          ActiveCell.Column), Cells(ActiveCell.Row + Nrows, _
          ActiveCell.Column)), Type:=xlFillDefault
    End If
End Sub

To use the macro, select the cell you want to use in the fill, then run it. You are asked for how many cells you want to fill, and then the macro dutifully fills that many cells down (a positive value) or up (a negative value). If you enter a negative value that is greater than the number of rows above the current cell, then the operation adjusts the number of rows to fill.

The benefit to using a macro-based approach is that you don't have to calculate the range of cells you want to fill. You can simply specify how many, and the macro takes care of the rest. If you assign the macro to a shortcut key, then you can invoke it with a single keystroke.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13514) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.