[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Simple AutoFill question.

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
RWayneH
Asked:
RWayneH
  • 9
  • 6
1 Solution
 
Glenn RayExcel VBA DeveloperCommented:
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
 
RWayneHAuthor Commented:
This did not work... is there an easy way like:   Selection.AutoFill Destination:=Range("F2:F")

which did not work either.
0
 
RWayneHAuthor Commented:
it is a number that I want to increment...  that is why I use  F2: to?  no formulas
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
RWayneHAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
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
 
Rob HensonFinance AnalystCommented:
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
 
RWayneHAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
RWayneHAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
RWayneHAuthor Commented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
 
RWayneHAuthor Commented:
Thanks for the help.
0
 
Glenn RayExcel VBA DeveloperCommented:
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.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now