PDA

View Full Version : [SOLVED] Next empty Dynamic row



Justinlabenne
12-04-2004, 08:46 AM
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
12-04-2004, 03:58 PM
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
12-04-2004, 05:39 PM
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
12-04-2004, 07:53 PM
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
12-05-2004, 01:07 AM
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
12-05-2004, 01:52 AM
LastRow and LastCol would make only one cell. How about this:


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

Justinlabenne
12-05-2004, 02:13 AM
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
12-05-2004, 03:30 AM
You're Welcome :)

Take Care