Next empty Dynamic row

Thanks again DRJ for solving my post on how to select and copy the next emtpy range. Since that post is marked solved I figured I would start a new thread. Since I have that, Is there any way to do this to select the last filled row in a range where the column's might be varying lengths?

Ex: Last filled row is row 6, Columns A:C

Next time macro is run the Last filled row is row 7, but Columns A:E

Any ideas on how to get this to extend to the last filled column in the last filled row? Not a big deal, just figured it would be a great value in the future.

Thanks,

Hi,

Are you saying that you want to get all the data on the sheet? In your above example, A1:E7, where E7 is the last column used to the right, and row 7 is that last row used?

If so, try the running the following:

Code:

---

Sub test()
activesheet.usedrange.select
End sub

---

If it highlights the right range, you can easily change the .select to .copy

HTH,

Sorry, not quite clear enough, just the "Last used row" Here is the code DRJ gave me to copy the last filled row from Col A to C. If the last non-empty row is in row 7 then this code would copy A7:C7. I am curious if it's possible to select the last non-empty row and go across as many columns as there is data.

Ex: Row 7 has Col, A-B-C filled with data
Next time macro is run, row 8 is the last non-empty row, but it has Col A-B-C-D-E
filled with data.

I am curious if the range selection process can change to read across how many columns have data in the last non-empty row?

Code:

---

Sub CopyShort()
LastRow = Sheets("ws1").Range("A65536").End(xlUp).Row
    Range("A" & LastRow & ":C" & LastRow).Copy
    Sheets("ws2").Select
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End Sub

---

Try this:

Code:

---

Option Explicit
 
 Sub Macro1()
Dim Row            As Long
 Dim Col            As Long
Row = Cells.Find(What:="*", LookIn:=xlValues, searchdirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Col = Cells.Find(What:="*", LookIn:=xlValues, searchdirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    Range("A1:" & Cells(Row, Col).Address).Copy
End Sub

---

This copied the entire used range when I ran it, not quite but still good to know, Not sure if this is really a feasible question, I can do what I am asking using Input box method, to select a single row at a time for the length of the column of data, I have attached a small file with a possibly clearer outline as to what I am thinking: Here is a non-working code I have been trying to modify, maybe these will give a better insight as to an answer, Thanks for everyone's ideas and time.

Code:

---

Sub DontKnow()
    LastRow = Sheets("ws1").Range("A65536").End(xlUp).Row
    lastCol = Sheets("ws1").Range("IV" & LastRow).End(xlToLeft).Column
Range(cells(LastRow, lastCol)).Copy
End Sub

---

LastRow and LastCol would make only one cell. How about this:

Code:

---

Range("A" & LastRow & ":" & Cells(LastRow, LastCol).Address).Copy

---

Dude, you are awsome, I didnt know if it was possible or not, but you just nailed it. Exactly what I was looking for, I will mark this one solved. Truly awesome, thanks again Jacob.

You're Welcome :)

Take Care