CODEWITHSUNDEEP
excelvbaparsingcopy
actuallife
actuallife

Reputation: 89

How to autofill 'x' number of rows using VBA in Excel

The overall idea of what I'm doing is taking in user input from a UserForm, adding that data to a single row, and then I want to copy that row 'x' number of times directly below that original row. The 'x' number of rows in this situation is also determined by values entered in by the user.

For example, say the user fills out the UserForm and the 'x' they chose was 5. My VBA code would take the info they entered in, put it on row 2, and then basically copy/paste that exact row on the next 4 rows down. Then end result would look something like this (EXCEPT EVERY CELL IN COLUMN C WOULD BE '44', I'M NOT SURE WHY THAT COLUMN DECIDED TO INCREMENT BY 1, BUT THATS ANOTHER THING I NEED HELP FIXING):

enter image description here

I'm assuming I can write VBA code to copy and paste the row 'x' amount of times, but I think autofilling would be easier since it will be the same values for every line. I just don't know how to specify a range that will change depending on what the user enters in. Here is my code that autofills for a specific range, I'm not sure how to change it to a varying range


numOfLines = (LastL - FirstL) + 1    'don't worry about how I get this, it's a number that I know is correct

   With Sheets("QuoteCSV")
    
        Sheets("QuoteCSV").Select
        
        ' Get the current row
        Dim curRow As Long
        If .Range("B1") = "" Then
            curRow = 1
        Else
            curRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
        End If
        
        ' Add items to the first row (row 2)
        .Cells(curRow, 1) = fson
        .Cells(curRow, 2) = fdiv
        .Cells(curRow, 3) = fcnum
        .Cells(curRow, 4) = fcponum
        .Cells(curRow, 5) = frdate
        .Cells(curRow, 6) = cname
        .Cells(curRow, 7) = add1
        .Cells(curRow, 8) = add2
        .Cells(curRow, 9) = city
        .Cells(curRow, 10) = state
        .Cells(curRow, 11) = zip
        
        
        'Now I want to take that row I just made and autofill it down 'numOfLines' rows

        Range("A2:K2").Select
        Application.CutCopyMode = False
        Selection.AutoFill Destination:=Range("A2:K6"), Type:=xlFillDefault 
        'need to change this line to use numOfRows somehow instead of hard-coded range
        
        
    End With
    ```

Any help is appreciated!

Upvotes: 3

Views: 1255

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Another way which doesn't require any autofilling. You fill the range in one go.

.Range(.Cells(curRow, 1), .Cells(numOfLines, 1)).Value = fson
.Range(.Cells(curRow, 2), .Cells(numOfLines, 2)).Value = fdiv
.Range(.Cells(curRow, 3), .Cells(numOfLines, 3)).Value = fcnum
.Range(.Cells(curRow, 4), .Cells(numOfLines, 4)).Value = fcponum
.Range(.Cells(curRow, 5), .Cells(numOfLines, 5)).Value = frdate
.Range(.Cells(curRow, 6), .Cells(numOfLines, 6)).Value = cname
.Range(.Cells(curRow, 7), .Cells(numOfLines, 7)).Value = Add1
.Range(.Cells(curRow, 8), .Cells(numOfLines, 8)).Value = Add2
.Range(.Cells(curRow, 9), .Cells(numOfLines, 9)).Value = city
.Range(.Cells(curRow, 10), .Cells(numOfLines, 10)).Value = State
.Range(.Cells(curRow, 11), .Cells(numOfLines, 11)).Value = zip

Upvotes: 2

BigBen
BigBen

Reputation: 49998

Perhaps instead of using AutoFill, use Resize:

With .Range("A2:K2")
    .Resize(numOfRows).Value = .Value
End With

Upvotes: 6

Related Questions