VBA fill down

chloedee1980

New Member
Joined
Jul 3, 2019
Messages
1
I have code set up to select rows and can then fill down, but I want column C to fill series and the other columns to copy down.

The Range will always be different and so will the number of rows selected.

Is there a way of doing this?

Code:
Private Sub CommandButton1_Click()

Dim lRows As Long
lRows = TextBox1.Value
ActiveCell.Offset(1).Resize(lRows, 1).EntireRow.Insert
ActiveCell.EntireRow.Select
Range(ActiveCell, ActiveCell.End(xlDown).Offset(-1, 0)).EntireRow.Select
Selection.FillDown


   
   


Unload Me


End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Code not tested, but should work
Code:
Private Sub CommandButton1_Click()
'/////////////////////////////////////////////////
'//  Inserts required nr of rows (specified in  //
'//  Textbox1) at the current location in sheet //
'//  then copies columns down, only column C is //
'//  autofilled.                                //
'/////////////////////////////////////////////////
    Dim lRows As Long, lCols As Long
    Dim rAct As Range
    
    'set rAct to cell An where n is activecell row
    Set rAct = Cells(ActiveCell.Row, "A")
    
    'get the number of rows and columns to act on
    lRows = TextBox1.Value
    lCols = rAct.CurrentRegion.Columns.Count
    
    With rAct
        'Add required nr rows below rAct
        .Offset(1).Resize(lRows, 1).EntireRow.Insert
        'Fill down all columns
        .Resize(lRows + 1, lCols).FillDown
        'Now Autofill column C
        .Offset(0, 2).Resize(lRows + 1, 1).AutoFill
    End With
    
    Unload Me
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top