VBA Code to Copy All USed Rows

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,551
Office Version
  1. 365
Platform
  1. Windows
How do I change the code below so that all used rows are copied, not the static range?

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Range("A1:F20").Select
Selection.Copy

My data could end in row 31 or 11, which is why I need a dynamic range.
[/FONT]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Despite the various possible issues maybe...
Code:
ActiveSheet.UsedRange.Copy
 
Upvote 0
How about
Code:
Range("A1").CurrentRegion.Copy
or
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 5)).Copy
 
Upvote 0
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:
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
When I get back in I will hopefully post some code that doesn't retain a memory as UsedRange and LastCell does.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top