+ Reply to Thread
Results 1 to 5 of 5

AutoFill of Dynamic Range

Hybrid View

  1. #1
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    AutoFill of Dynamic Range

    I'm trying to convert the static value of "X372:BV372" in line 1 on the screenshot into a dynamic range based on last row, but I'm getting an "AutoFill method of Range class failed" with my modifed syntax in line 2 on the screenshot..

    Sub Macro5()
        With Sheets("Data03")
            lrow = .Range("A" & Rows.Count).End(xlUp).row
            .Range("X" & lrow + 1).Formula = "=SUM(X3:X" & lrow & ")"
            'Selection.AutoFill Destination:=Range("X372:BV372"), Type:=xlFillDefault <---- Recorded with the Excel Macro recorder
            Selection.AutoFill Destination:=Range("X" & lrow + 1 & ":BV" & lrow + 1), Type:=xlFillDefault <---- My modified syntax that errors out
        End With
    End Sub
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: AutoFill of Dynamic Range

    What was the selection at the beginning, it means how many cells did you select from column X when you start to drag over the columns
    Else if 2 cells where covered (columns X and Y) then try
    Option Explicit
    
    Sub Macro5()
    Dim lrow  As Long
        With Sheets("Data03")
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("X" & lrow + 1).Formula = "=SUM(X3:X" & lrow & ")"
            'Selection.AutoFill Destination:=Range("X372:BV372"), Type:=xlFillDefault   <---- Recorded with the Excel Macro recorder
            Range("X" & lrow + 1).Resize(, 2).AutoFill Destination:=Range("X" & lrow + 1 & ":BV" & lrow + 1), Type:=xlFillDefault    ' <---- My modified syntax that errors out
        End With
    End Sub
    Last edited by PCI; 12-17-2013 at 05:29 PM. Reason: additional info added

  3. #3
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: AutoFill of Dynamic Range

    after i read your comment/question, I realized what was the problem. I didn't have the cell selected.

    new code sample...
    Sub Macro5()
    Dim lrow  As Long
        With Sheets("Data03")
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("X" & lrow + 1).Formula = "=SUM(X3:X" & lrow & ")"
            .Range("X" & lrow + 1).Select
            Range("X" & lrow + 1).Resize(, 2).AutoFill Destination:=Range("X" & lrow + 1 & ":BV" & lrow + 1), Type:=xlFillDefault
        End With
    End Sub
    thank you for the help

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: AutoFill of Dynamic Range

    Below will do the same..

    Sub Macro5()
        With Sheets("Data03")
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("X" & lrow + 1 & ":BV" & lrow + 1).Formula = "=SUM(X3:X" & lrow & ")"
        End With
    End Sub
    and for your query..

    Sub Macro5()
        With Sheets("Data03")
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("X" & lrow + 1).Formula = "=SUM(X3:X" & lrow & ")"
            .Range("X" & lrow + 1).Select
            'Selection.AutoFill Destination:=Range("X372:BV372"), Type:=xlFillDefault <---- Recorded with the Excel Macro recorder
            Selection.AutoFill Destination:=Range("X" & lrow + 1 & ":BV" & lrow + 1), Type:=xlFillDefault ' <---- My modified syntax that errors out
        End With
    End Sub
    Last edited by Debraj Roy; 12-17-2013 at 06:00 PM.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  5. #5
    Registered User
    Join Date
    04-19-2013
    Location
    midwest usa
    MS-Off Ver
    2007, 2010
    Posts
    16

    Re: AutoFill of Dynamic Range

    this works even better. thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Autofill using a Macro when range is dynamic
    By CPSmurf in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 09-03-2013, 05:54 PM
  2. VBA Code - Autofill with a Dynamic Range
    By Jurij in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2013, 02:27 AM
  3. Need a macro to autofill a dynamic range
    By missmea2005 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-13-2012, 09:15 AM
  4. Vlookup and autofill a dynamic range
    By ajay1967 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-20-2012, 07:36 PM
  5. Autofill using Dynamic Range (Last Column) and Last Row
    By goldenr2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-05-2011, 11:21 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1