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

VBA - Autofill To Last Row of Data

This is a discussion on VBA - Autofill To Last Row of Data within the Excel Questions forums, part of the Question Forums category; I'm trying to write a macro that allows me to autofill columns D:G starting always starting at D7 and going ...

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

    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
    26,996

    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
    11,638

    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
    332

    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
    6,304

    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
    332

    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
    26,996

    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
    11,638

    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
    332

    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
    11,638

    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.

Page 1 of 2 12 LastLast

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