autofill formula to end

[ShellyTTC]

I can autofill a formula in cell c2 to the known "desired" ag2 cell but the problem for me comes on the next step of autofill going to the bottom (simulate double click "+" with selected c2:ag2). I'm trying to put this in a macro that does a number of things and each time this is used the sheet can have a different number of rows. How do I make this code with a variable to the end? next time the range might need to go to AG250 or AG196. I am thinking that some how I need to use the .End(x1Down) but I'm not sure how to format/syntax it in the code below. Your help is greatly appreciated. (still learning lots) Shelly

Range("C2:AG2").Select
Selection.AutoFill Destination:=Range("C2:AG115")
Range("C2:AG115").Select

 

[SirJB7]

Hi, ShellyTTC!

When you face an issue like this, the built-in macro recorder may come in your rescue. This is the crude (not tweaked) code that I got:

Option Explicit
 
Sub Macro3()
'
' Macro3 Macro
'
 
'
    Range("C2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.AutoFill Destination:=Range("C2:AG115"), Type:=xlFillDefault
    Range("C2:AG115").Select
    Range("C2").Select
End Sub

But what I don't understand is what did you mean when wrote that you wanted to go to the bottom, are there any empty cells in the target range from row 3 in advance?

Regards!

 

[ShellyTTC]

The code I put in here was from the macro but I don't know how to tweak it to accomodate the situation where different sheets can have a different number of rows therefore =Range("c2:ag115") would be incorrect. How do I fix the AG115 to be a variable that goes to the end depending on how many rows the sheet has? Thanks for trying to help! I still need a solution. -Shelly

 

[ShellyTTC]

I was wondering if I could use count(a:a) in another cell and then concatenate the ag and results into the range("c2:??")

 

[SirJB7]

Hi, ShellyTTC!

Give a look at the attached file. The code is this:

Option Explicit
 
Sub BicycleFill()
    '
    ' constants
    Const ksStartingCell = "I27"
    Const ksDirection1 = "R"
    Const ksDirection2 = "D"
    Const ksDirections = "X U D L R"
    '
    ' declarations
    Dim rngSource As Range, rngTarget As Range
    Dim iDirection As Variant, sDirection As Variant
    Dim iDir1 As Integer, iDir2 As Integer
    Dim I As Integer, bOk As Boolean
    '
    ' start
    '  directions
    iDirection = Array(0, xlUp, xlDown, xlToLeft, xlToRight)
    sDirection = Split(ksDirections)
    iDir1 = iDirection((InStr(ksDirections, ksDirection1) - 1) / 2)
    iDir2 = iDirection((InStr(ksDirections, ksDirection2) - 1) / 2)
    '  ranges
    Set rngSource = Range(Range(ksStartingCell), Range(ksStartingCell).End(iDir1))
    Set rngTarget = Range(rngSource, rngSource.End(iDir2))
    '
    ' process
    rngSource.AutoFill Destination:=rngTarget, Type:=xlFillDefault
    Range(ksStartingCell).Select
    '
    ' end
    Set rngTarget = Nothing
    Set rngSource = Nothing
    Beep
    '
End Sub

It uses 3 parameters, a starting cell, a source direction 1 and a target direction 2.
In your case they should be "C2", "R" and "D".
Directions can have values U, D, L, R (for up, down, left, right).

I tested it from cell I27 in the 8 logic possible directions: UL, UR, DL, DR, LU, LD, RU, RD, and it worked fine. It filled the cells inner to the yellow shaded area (first empty cells in each direction). When specifying the end direction take care that if there's is no empty cells from starting cell to the end of target direction, the .End(direction) method will go until worksheet boundaries, i.e., if no empty cells from C3 and down it'll try to fill until row 1048576, probably making Excel crash because of resource abuse. So in your case you should have filled the range "C2:C115" previously or filled the cells C2 and C115 to mark the end of the filling process.

Just advise if any issue.

Regards!

 

[Luke M]

Hi SirJB7
For the code, the language needs to be "VB" not "VBA". I've tweaked them for you.

 

[SirJB7]

@Luke M
Hi!
Thanks, but I even if I like much more VB than VBA, with this forums software I prefer VBA. I don't care too much of the color for keywords, which it's useful, but I don't bear the wrong indenting: if I set default indent to 4, why does someware (mix of someone and software) changes to 3 for 1st line after each 1st comment, even if it's a comment?
Regards!
PS: Thanks again for the intention, but I'd change it to VBA (which I assume that it's the same as don't specifying anything since VBA isn't in the list of available languages... and BTW, when clicking in the insert code button that pops up a dialog there're only General Code, PHP and HTML, even we know that a lot more are supported, e.g. VB)

 

[Smallman]

Hi

I can’t put this more simply. Usually in these situations you have a row that is filled fully. What I do is use this row as a holder for the last row that you will need. Lets use Col A as this row, this will be represented by the variable lr (short for last row. Now lets just take your recorded code above and clean it up.

Option Explicit
 
Sub temp()
Dim lr As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("C2:AG2").AutoFill Range("C2:AG" & lr)
End Sub

Now you trap your last row on any sheet every day of the week. You could reduce this further when you feel more comfortable with vb to the following.

Sub temp2()
 
    Range("C2:AG2").AutoFill Range("C2:AG" & Range("A" & Rows.Count).End(xlUp).Row)
 
End Sub

Take care

Smallman

 

[ShellyTTC]

I am so glad I found you guys. What an awesome resourse!!! I want to thank all of you for responding. I found a simple solution I like and will share. I found it before I saw the additional post.

Selection.autofill Destination:=Range("C2:AG" & Range("A" & Rows.Count).End(xlUp).Row)
This works exactly as I need it to. I knew this is what I wanted to do if possible I just didn't know the proper syntax or code.

Again, thank you all for responding and giving me so many options to explore. Have a fun day! Until next time,
Shelly

SOLVED

 

[Smallman]

Hi Shelly

Thanks for sharing your solution. In vb it is (mostly) not necessary to select anything. As your knowledge improves try to move away from Selecting data and performing actions on the selection the same way you would in native XL. Your code will run more smoothly and you will need less code to do the same set of tasks.

Once again thanks for returning to share. It is a magic round about!!!

Take care

Smallman