Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: VBA - Autofill To Last Row of Data

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Posts
    337

    Default VBA - Autofill To Last Row of Data

    I'm trying to write a macro that allows me to autofill columns D:G starting always starting at D7 and going until there is no more data found in columns A:C in any subsequent rows. So, in this example the last piece of data is row 578, but next time it may be 800. How do I write the autofill so that it continue until no matter data is left?

    Thanks!

    Code:
    Range("D6").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Range("D7:G7").Select
        Application.CutCopyMode = False
        Selection.autofill Destination:=Range("D7:G578")
        Columns("F:G").EntireColumn.AutoFit
        Columns("D:D").EntireColumn.AutoFit

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    39,763

    Default Re: VBA - Autofill To Last Row of Data

    Find the last row of data, set it equal to a variable, and use that variable for your row reference.

    If you search the forum archives for finding the last row with data, you should be able to find a ton of hits (it usually comes up multiple times a day!).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,419

    Default Re: VBA - Autofill To Last Row of Data

    Try something like this...
    Code:
        Range("D6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        Lastrow& = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Range("D7:G7").AutoFill Destination:=Range("D7:G" & Lastrow)
        Columns("F:G").EntireColumn.AutoFit
        Columns("D:D").EntireColumn.AutoFit

  4. #4
    Board Regular
    Join Date
    Jun 2009
    Posts
    337

    Default Re: VBA - Autofill To Last Row of Data

    Thanks guys! I was able to find another solution that worked. Here is the code for anybody with similar issues.

    Code:
    Dim LR As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
     
        Range("D6").Select
        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Range("D7:G7").Select
        Application.CutCopyMode = False
        Selection.autofill Destination:=Range("D7:G" & LR)
        Columns("F:G").EntireColumn.AutoFit
        Columns("D:D").EntireColumn.AutoFit

  5. #5
    Board Regular jim may's Avatar
    Join Date
    Jul 2004
    Location
    Roanoke, VA
    Posts
    7,054

    Default Re: VBA - Autofill To Last Row of Data

    Aren't you ONLY testing Column B for LR here? What if Column A or C has data beyond Column B?

    FWIW..
    Here's code that will return the last nonblank row whether in Col A, B or C...

    Code:
    Sub MyLastRowUsed()
    Set Rng = Range("A:C")
      maxrow = 1
      For Each col In Rng.Columns
        lrow = Rng.Cells(Rows.Count, _
        col.Column).End(xlUp).Row
        If lrow > maxrow Then
          maxrow = lrow
        End If
      Next
      MsgBox maxrow
    End Sub
    To display your spreadsheet data use either MrExcel HTML Maker or Excel Jeanie

  6. #6
    Board Regular
    Join Date
    Jun 2009
    Posts
    337

    Default Re: VBA - Autofill To Last Row of Data

    I appreciate the concern with only using column B and I did notice that as well. The fortunate thing is that the data is Column A, B and C will always end on the same row. I used B; however, because it has data in every row unlike column A. Column C could have sufficed. Thank you for the code. I will try to incorporate it.

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    39,763

    Default Re: VBA - Autofill To Last Row of Data

    I believe that AlphaFog's code already take it into consideration, and will take the last row with data in columns A-C without the use of a loop.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,419

    Default Re: VBA - Autofill To Last Row of Data

    Just to let you know that this will also find the last used row in columns A or B or C no matter which column has the last row used.
    Code:
        Lastrow = Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

  9. #9
    Board Regular
    Join Date
    Jun 2009
    Posts
    337

    Default Re: VBA - Autofill To Last Row of Data

    Does the * = blank data? I've never seen that before.

  10. #10
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    15,419

    Default Re: VBA - Autofill To Last Row of Data

    The asterisk means any data. It is a wildcard.

    Range("A:C").Find Look in range columns A to C
    "*" Look for what? Asterisk is a wild card. (Look for anything)
    SearchOrder:=xlByRows search by rows as opposed to columns
    SearchDirection:=xlPrevious search bottom up as opposed to top down
    .Row return the row number of the first item found.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com