VB for Auto-Fill

**adgjqetuo** · 06-08-2009, 12:29 PM

Can someone help me tweak this code? It's telling me I need an object?

I need it to autofill just as if you selected range A1 and A2 and double clicked that little square on the bottom right hand side of the cell (so it stopps auto-filling at the last active cell in B)

Sub Dvide()
Dim RangeA As Range
If MsgBox("Complete this action?", vbYesNo) = vbYes Then
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Set RangeA = Range("A1:A2")
RangeA.AutoFill Destination:=Range(RangeA, Cells(RamgeA.End(xlDown).Row, RangeA.Column))
End Sub

**JBeaucaire** · 06-08-2009, 01:21 PM

Maybe this:

Sub Divide()
Dim LR As Long
    If MsgBox("Complete this action?", vbYesNo) = vbYes Then
        Range("A1") = 1
        Range("A2") = 2
        LR = Range("A2").End(xlDown).Row
        Range("A1:A2").AutoFill Destination:=Range("A1:A" & LR)
    End If
End Sub

Since the one above overlaps the next section (which is what your original macro seemed to do), this version does NOT do that:

Sub Divide()
Dim LR As Long
    If MsgBox("Complete this action?", vbYesNo) = vbYes Then
        Range("A1") = 1
        Range("A2") = 2
        LR = Range("A2").End(xlDown).Row - 1 
        Range("A1:A2").AutoFill Destination:=Range("A1:A" & LR)
    End If
End Sub

**Kenneth Hobson** · 06-08-2009, 01:34 PM

You probably need to explain what you mean by last active row in column B. When you use Select, you have changed the active cell. Select is seldom needed.

Try a manual fill and you will see the limitations of fill. Trying to fill a range that is not legal will result in an error. Comment the line above and uncomment the line below to see this error.

Sub Dvide()
  Dim RangeA As Range
  If MsgBox("Complete this action?", vbYesNo) = vbYes Then
      Range("A1").FormulaR1C1 = "1"
      Range("A2").FormulaR1C1 = "2"
      Set RangeA = Range("A1:A2")
      RangeA.AutoFill Destination:=Range(RangeA, Range("B" & RangeA.Rows.Count))
      'RangeA.AutoFill Destination:=Range(RangeA, Range("B3"))
  End If
End Sub

**adgjqetuo** · 06-08-2009, 02:27 PM

Thanks for the responses!

JBeaucaire: Yours worked but it just auto-fills down 60,000+ Cells - I need it to stop at the last active cell in column B (Just like if you double clicked that little square)

Kenneth: I tried your code, and couldn't get it to work.

**JBeaucaire** · 06-08-2009, 03:30 PM

Which column can be evaluated (examined) to determine the last row? Column B, for instance? You haven't posted a sheet so we're working blind here.

In the red line of code here, change the column listed (blue) to point to the column where data exists already and can be used to spot the last row to copy down to:

Sub Divide()
Dim LR As Long
    If MsgBox("Complete this action?", vbYesNo) = vbYes Then
        Range("A1") = 1
        Range("A2") = 2
        LR = Range("A" & Rows.Count).End(xlUp).Row   ' Change column reference as needed
        Range("A1:A2").AutoFill Destination:=Range("A1:A" & LR)
    End If
End Sub