Dynamic Range For top and bottom

[Felix1980]

Hello,
I'm trying to autofill some cells but I do not want to autofill from the top of the column I need to only fill from the first empty cell in column C down to the last used cell in column A. I've tried several different iterations the following is currently what I've attempted but it is not working. Any help is appreciated.

    Dim lastRow As Long
    Dim FirstLastRow As Long
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    FirstLastRow = Range("C" & Rows.Count).End(xlUp).Row
    
    Range("C2").AutoFill Destination:=Range(FirstLastRow, lastRow)

 

[Felix1980]

Hello Fluff,

Here is the full code. It is inserted for the close button on the userform.

Private Sub cmdMWClose_Click()
Unload Me
Sheet2.Activate


    Dim rng As Range
    Dim sht As Worksheet




Set sht = Sheet2
Set rng = sht.Range("A1").CurrentRegion


    With rng.Borders
        .LineStyle = xlContinuous
        .Color = xlBlack
        .Weight = xlThin
    End With




With Range("C" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("C2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 2))
End With
      
With Range("E" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("E2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 4))
End With
    
With Range("F" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("F2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 5))
End With


With Range("G" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("G2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 6))
End With


With Range("I" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("I2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 8))
End With


With Range("J" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("J2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 9))
End With
    
With Range("K" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("K2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 10))
End With


With Range("L" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("L2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 11))
End With


With Range("M" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("M2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 12))
End With


With Range("N" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("N2").FormulaR1C1
   .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 13))
End With


End Sub

For the most part it is working as intended, but I've been shown two issues that I'm confused about.

1. When a new user uses the workbook on their computer they get a debugging issue. It highlights the Autofill line on the first With statement. However, if the user "X"s off VBA (restarting the code) and opens the user form again then closes, this time the debugger highlights the autofill line in the second With statement. This continues for each with statement (10 in total). Once the debugger has highlighted each of those lines it no longer gives any error, even if the user closes the workbook and restarts. I don't understand.

2. If the user opens the userform then closes it without adding any data the code is still adding the formulas to the bottom empty line. I've tried a couple If statements but can't seem to get it to stop.

Any understanding to these issues would be appreciated.

 

[Fluff]

For point 2, Add this before the with statements

If Range("A" & Rows.Count).End(xlUp).Row = Range("C" & Rows.Count).End(xlUp).Row Then Exit Sub

Point 1 seems very odd, what is the actual error that they get?

 

[Felix1980]

The error is :

Run-time error '1004':

AutoFill method of Range class failed

 

[Fluff]

Ok, will all the columns you want to filldown be the same length, before the code is run?

 

[Felix1980]

Yes they will. The user uses the userform to insert info into columns A, B, D and H. Then the code in the close button then grabs the formula from row 2 and adds it to columns C, E, F, G, I, J, K, L, M, and N to go down to the last used row in column A from the first empty cell in the corresponding columns.

 

[Fluff]

Ok, try this

Dim Lr As Long
Lr = Range("A" & Rows.Count).End(xlUp).Row
If Lr = Range("C" & Rows.Count).End(xlUp).Row Then Exit Sub

With Range("C" & Rows.Count).End(xlUp).Offset(1)
   .FormulaR1C1 = Range("C2").FormulaR1C1
   If Lr > .Row Then
      .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 2).Address)
   End If
End With
      
With Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(, 3)
   .FormulaR1C1 = Range("E2:G2").FormulaR1C1
   If Lr > .Row Then
      .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 6))
   End If
End With

With Range("I" & Rows.Count).End(xlUp).Offset(1).Resize(, 6)
   .FormulaR1C1 = Range("I2:N2").FormulaR1C1
   If Lr > .Row Then
      .AutoFill Range(.Address, Range("A" & Rows.Count).End(xlUp).Offset(, 13))
   End If
End With

 

[Felix1980]

Thank you Fluff! That appears to have fixed the issue.

 

[Fluff]

You're welcome & thanks for the feedback