Copy down to last row in table

[gheyman]

I am trying to write this code to find the last row that has data (in that specific column of a table) and copy it. Then paste it from that cell down to the last row of the table.

The code is copying the last row in my table - not the last row in column J that has data. Also the code is stoping there. It is not selecting the cells to paste into (yes my code does not have the step to paste - but its not even selecting.

Not that column J is a data validation list. So I dont know if thats the reason it thinks there is data all the way to the bottom of Row J. As of now my data, in column J only goes down to row 43 and there are 45 rows in my table.

Sub FillResourceStep3()

Dim LRResource As Long
Dim LRStep3

Sheets("Step 3").Select

LRResource = Cells(Rows.Count, "J").End(xlUp).Row
LRStep3 = Cells(Rows.Count, "C").End(xlUp).Row

Range("J" & LRResource).Copy
Range("J" & LRResource & ":" & "J" & LRStep3).Select




End Sub

Thank you for your time and help!

 

[RobP]

Try this instead of your last two lines of code :

Range("J" & LRResource).Copy Range("J" & LRResource, "J" & LRStep3)

 

[gheyman]

I appreciate the help, but that did not work. When I run the code, nothing happens. It does not copy or paste.

 

[RobP]

ok, I can't really see why it wouldn't copy or Paste. When I tested it, I had data in column C that say went down to row 10, then I had data in Row J that went down to row 5 only. This routine copied the last data in J5, into J6,7,8,9, and 10 ie. down to the bottom row of data in Col C.

Is my understanding of your problem the correct one ?

It might be something to do with tables, but I'm not too sure just yet.

Rgds
Rob

 

[My Aswer Is This]

I asked this same question several years ago on this forum and never received an answer.
Looking for the first empty cell in an Excel Table column. I believe that is what you're looking for.

 

[RobP]

Yes, re-testing it with an actual Table, I can see that the issue is with the lastrow finding the actual table last row, rather then the actual data in a column of the table.

Doing a little searching, I've yet to find anyone who has found any kind of solution to that problem, sorry.

Rgds
Rob

 

[Fluff]

You can find the last used row in a table like

   Dim Fnd As Range
   
   With Sheets("Step 3").ListObjects("Table1").ListColumns(10)
      Set Fnd = .DataBodyRange.Find("*", , , , xlByRows, xlPrevious, , , False)
      Fnd.Select
   End With

 

[RobP]

Do you have to actually select your (possibly unknown) table somehow and name it "Table1" beforehand to enable this to work ?

thanks
Rob

 

[Fluff]

You can just change the name of the table to suit, whilst there is very rarely any need to select anything, if you do that sheet will need to be active.

 

[Fluff]

To fill down the last used row in the table, try

   Dim Fnd As Range
   
   With Sheets("Step 3").ListObjects("Table1")
      Set Fnd = .ListColumns(10).DataBodyRange.Find("*", , , , xlByRows, xlPrevious, , , False)
      Range(Fnd, .Parent.Range("H" & Rows.Count).End(xlUp)).FillDown
   End With