-3

I have a vba code that generates an email. I would like the subject to be the data from the first and last cells in my list. The thing is, my list isnt of a set length, sometimes it contains 5 pieces of data sometimes 8 etc. How do i tell vba to pick the first and last cell no matter the length of the list?

thanks

user1234
  • 111
  • 9
  • Look for the last used row in your list. Collect the values in a loop all together and paste it in the .subject of your email. – JvdV May 29 '18 at 10:00

2 Answers2

1

For me, best practice is to just have cells on your sheet that calculate the first and last row (different ways you can do that), then give those cells a range name such as FirstRow and LastRow. In your vba then you refer to these cells to make your code dynamic.

e.g: 

firstRow = Range("FirstRow)
lastRow = Range("lastRow")
test = range(cells(firstRow,lastRow))

-- Note I have not written VBA in many many years so am writing the above from memory so it may be not be exact.

Of course you can do it all entirely in VBA using the xlDown method mentioned previously but I prefer the transparency of it being on the main page so that easily spot if something breaks.

Reddspark
  • 6,934
  • 9
  • 47
  • 64
0
Range("A1").End(xlDown).Value

Where the cell is where you want to start and the End part moves all the way to the end

user1234
  • 111
  • 9