VBA Code to Copy All USed Rows
Results 1 to 10 of 10

Thread: VBA Code to Copy All USed Rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Location
    The OC, where else?
    Posts
    1,292
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to Copy All USed Rows

    How do I change the code below so that all used rows are copied, not the static range?

    Range("A1:F20").Select
    Selection.Copy

    My data could end in row 31 or 11, which is why I need a dynamic range.
    I am using the beautiful Excel 2016!

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    Despite the various possible issues maybe...
    Code:
    ActiveSheet.UsedRange.Copy
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,725
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    How about
    Code:
    Range("A1").CurrentRegion.Copy
    or
    Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 5)).Copy
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    Is all your data continuous (no completely blank rows or columns in the middle of the data)?

    If so, CurrentRegion works well, i.e.
    Code:
    Range("A1").CurrentRegion.Copy
    Otherwise, you can find the last cell and use that, i.e.
    Code:
        Dim ad As String
        ad = Range("A1").SpecialCells(xlLastCell).Address
        
        Range("A1:" & ad).Copy

    EDIT: Too slow a typist, I am!
    Last edited by Joe4; Aug 20th, 2019 at 11:35 AM.
    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!"

  5. #5
    Board Regular
    Join Date
    Aug 2009
    Location
    The OC, where else?
    Posts
    1,292
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    Quote Originally Posted by Joe4 View Post
    Is all your data continuous (no completely blank rows or columns in the middle of the data)?

    If so, CurrentRegion works well, i.e.
    Code:
    Range("A1").CurrentRegion.Copy
    Otherwise, you can find the last cell and use that, i.e.
    Code:
        Dim ad As String
        ad = Range("A1").SpecialCells(xlLastCell).Address
        
        Range("A1:" & ad).Copy
    EDIT: Too slow a typist, I am!
    Thank you for mentioning that as I should have told you I do have blank rows. So this code works for me except it is copying column G, which is completely, 100% blank (no formula, no nothing).
    I am using the beautiful Excel 2016!

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    Thank you for mentioning that as I should have told you I do have blank rows. So this code works for me except it is copying column G, which is completely, 100% blank (no formula, no nothing).
    So, you have no data beyond column F?

    If it is also selecting column G, you have some data formats there were extended out there, or something was in the previous that was deleted since your last save.

    If your rows are fine, and you know the data will always end in column F, you can use:
    Code:
        Dim lr As Long
        lr = Range("A1").SpecialCells(xlLastCell).Row
        
        Range("A1:F" & lr).Copy
    Alternatively, if for every row that you want to copy, column A is populated, then Fluff's reply may work for you too.
    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!"

  7. #7
    Board Regular
    Join Date
    Aug 2009
    Location
    The OC, where else?
    Posts
    1,292
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    I have no data or formatting in column G, plus I just deleted it but it still gets copied with this code; however, I just used your revised code with variable lr as long and it works beautifully!

    Thank you!
    Last edited by Justinian; Aug 20th, 2019 at 02:05 PM.
    I am using the beautiful Excel 2016!

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,383
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    You are welcome!

    plus I just deleted it but it still gets copied with this code
    Just as an FYI, if you delete the whole column, and then re-save your file (which resets the end points), that code should work then.
    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!"

  9. #9
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    When I get back in I will hopefully post some code that doesn't retain a memory as UsedRange and LastCell does.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,995
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Code to Copy All USed Rows

    If you are still having issues try the codes below, the first doesn't include formulas returning "" in the range, the second one does include formulas returning "" in the range.


    Code:
    Sub FindUrangeVal()
        Dim lc As Long, lr As Long
    
        lc = Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious, False).Column
        lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False).Row
    
        Range("A1", Cells(lr, lc)).Select
    End Sub


    Code:
    Sub FindUrangeFor()
        Dim lc As Long, lr As Long
    
        lc = Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious, False).Column
        lr = Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    
        Range("A1", Cells(lr, lc)).Select
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

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
  •