Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Simple AutoFill question.

Posted on 2016-08-11
16
Medium Priority
?
89 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 2000 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

722 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