Solved

Simple AutoFill question.

Posted on 2016-08-11
16
67 Views
Last Modified: 2016-08-25
How do I make this dynamic?  So it just autofills as far down as it is allowed to?  Starting range is F2:F3 and A2:A3

   Range("F2:F3").Select
    Selection.AutoFill Destination:=Range("F2:F7")
    Range("A2:A3").Select
    Selection.AutoFill Destination:=Range("A2:A7")

Open in new window

0
Comment
Question by:RWayneH
  • 9
  • 6
16 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41752744
I always use a variable to indicate the last usable row in a data set and then include it in the AutoFill method.  For example:
Sub samplefill() 
    Dim intLastRow As Integer
    intLastRow = Range("A" & Cells.Rows.Count).End(xlUp).Row

    Range("J2").Formula = "=DATEVALUE(E2&"" ""&F2&"", ""&I2)+TIMEVALUE(G2)"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & intLastRow)
End Sub

Open in new window


You might define the last row as Long instead of Integer if you expect it to be a value higher than 65536.

-Glenn
0
 

Author Comment

by:RWayneH
ID: 41752755
This did not work... is there an easy way like:   Selection.AutoFill Destination:=Range("F2:F")

which did not work either.
0
 

Author Comment

by:RWayneH
ID: 41752760
it is a number that I want to increment...  that is why I use  F2: to?  no formulas
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:RWayneH
ID: 41752773
Oh I forgot to provide a key piece of info....  it is based on number of rows in column H,  need to autofill down as far as the number of rows that are filled.

Was trying: but had issues with getting the If to = True
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 1 And Target.Column = 8 Then
    Range("A2:A3").AutoFill Range("A2:A" & Target.Rows.Count + Target.Row - 1)
    Range("F2:F3").AutoFill Range("F2:F" & Target.Rows.Count + Target.Row - 1)
End If

End Sub

Open in new window

0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41752877
Are you trying to create a trend sequence of values like 1,2,3,4.... and so on down to the last used row?
0
 

Author Comment

by:RWayneH
ID: 41752948
Yes that is correct.  One column has 1,2,3,4,.... and I primed the pump by putting in the 1 and 2, then wrote an If state to remove second row if there is only one selected and if two rows exist it is fine.  However once we get >2 rows of data, I need to autofill them down.
0
 

Author Comment

by:RWayneH
ID: 41752967
The other column increments 100, 200, 300 .....  I am good doing it in one line.  I thought we could edit the line that the vba-recorder recorded?  Guess not.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41753494
Is your data set as a table?

If so, make column A a formula like =A1+1 so that it increments in whatever units you require.

As new data is added to the table the columns with formulae will copy down automatically, thus incrementing as required.

Even if not a table, there is an option for Formatting and Formulas to extend automatically. See image below.Excel options
0
 

Author Comment

by:RWayneH
ID: 41753739
F2 is 1 and F3 is 2.  A2 is 100 and A3 is 200.  Because the users may or may not have the setting in options, I would rather finish those columns with the macro.  There must be a way to complete the fill if there is a known vertical range in column H.  I thought that this was a simple edit of what the vba recorder recorded.  Quick simple question.  Guess not
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41754249
If your intent is to create a linear trend based on the two initial values, I'd just incorporate that in the VBA code rather than try to emulate the Excel Fill function.

Your sample code implies that this fill should be activated if the user selects a range of cells in column H (Target. Column = 8).  However, it doesn't check or force the fill range to be in the same rows as those selected by the user.  For example, if I select H7:H18, only cells A2:A13 and F2:F13 will be filled (i.e., 12 rows).  Is that your intent, or do you want the fill to be in the same rows the user selects?
0
 

Author Comment

by:RWayneH
ID: 41754934
This  is what I landed on... I just thought this would be a no brainer.. and we could just do it in one line with AutoFill.  this could probalbly be cleaned up a little...  There must be an easier way without having to loop through them until Not IsBlank Offset Active.Cell.  How can I do the same thing with having to use a Do While loop?

Range("F2").Select
ActiveCell.FormulaR1C1 = "100"
ActiveCell.Offset(1, 0).Select
Do While Not ActiveCell.Offset(0, 1) = ""
    'MsgBox ("It is not blank")
    ActiveCell.FormulaR1C1 = "=R[-1]C+100"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Select
Loop

Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
ActiveCell.Offset(1, 0).Select
Do While Not ActiveCell.Offset(0, 6) = ""
    'MsgBox ("It is not blank")
    ActiveCell.FormulaR1C1 = "=R[-1]C+1"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.Offset(1, 0).Select
Loop

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41757033
It can be much simpler than that.   You didn't answer my last question about the actionable range of the fill, so I'm going to presume that you want the filled values to be in the same rows that the user selects in column H.
Option Explicit
Dim lngLastRow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Columns.Count = 1 And Target.Column = 8 And Target.Rows.Count > 1 Then
        lngLastRow = Range("H" & Cells.Rows.Count).End(xlUp).Row
        Range("A2:A3").AutoFill Range("A2:A" & lngLastRow)
        Range("F2:F3").AutoFill Range("F2:F" & lngLastRow)
    End If
End Sub

Open in new window


See the attached workbook for an example.

Regards,
Glenn
EE_Q_28962875.xlsm
0
 

Author Comment

by:RWayneH
ID: 41757889
Can this be done outside of the  Worksheet_SelectionChange(    I am going to use to in a regular sub now and there is no longer a need if it on the Select  in?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41765389
Sorry for the delay; I was on jury duty last week.

Yes, you could use the above code in a regular subroutine, but if the user doesn't have a range of cells selected when it's run, nothing will happen.
0
 

Author Closing Comment

by:RWayneH
ID: 41770640
Thanks for the help.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41770894
You're welcome; glad I could provide a solution.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question