VBA Code to Copy All USed Rows

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,435
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]
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,892
Office Version
365, 2010
Platform
Windows, Mobile
Despite the various possible issues maybe...
Code:
ActiveSheet.UsedRange.Copy
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,835
Office Version
365
Platform
Windows
How about
Code:
Range("A1").CurrentRegion.Copy
or
Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 5)).Copy
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,707
Office Version
365
Platform
Windows
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:

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,435
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).
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,707
Office Version
365
Platform
Windows
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.
 

Justinian

Well-known Member
Joined
Aug 9, 2009
Messages
1,435
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:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,707
Office Version
365
Platform
Windows
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,892
Office Version
365, 2010
Platform
Windows, Mobile
When I get back in I will hopefully post some code that doesn't retain a memory as UsedRange and LastCell does.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
12,892
Office Version
365, 2010
Platform
Windows, Mobile
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,678
Messages
5,488,222
Members
407,632
Latest member
varunwalla

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top