Next empty Dynamic row

[Justinlabenne]

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,

[Ken Puls]

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:

Sub test()
activesheet.usedrange.select
End sub

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

HTH,

[Justinlabenne]

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?

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

[Jacob Hilderbrand]

Try this:

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

[Justinlabenne]

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.

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

[Jacob Hilderbrand]

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

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

[Justinlabenne]

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.

[Jacob Hilderbrand]

You're Welcome

Take Care