Solved

Simple AutoFill question.

Posted on 2016-08-11
16
48 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
Comment Utility
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
Comment Utility
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
Comment Utility
it is a number that I want to increment...  that is why I use  F2: to?  no formulas
0
 

Author Comment

by:RWayneH
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:RWayneH
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for the help.
0
 
LVL 27

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now