Often in VBA, you're dealing with ranges whose size changes all the time

• New data is added at the end
• How do you find the last cell ?
• How do you add data to the first free cell ?

Well, it just so happens that there is a cunning trick to help you out.

In this example, we'll look at how to save a share price on demand, and to put the most recent share price at the bottom.

Here's a screen shot of our data:

Here's a screen shot of our VBA Code:

What's going on then ?

1. I set MyCell to equal the cell in the next row after the cell that is the first populated cell looking upwards from cell C65000 (bit of a mouthful!)
2. Why 65000 ?... I use that as it's near the limit of row 65536 in Excel 2003, and easier to type than 65536
3. A cleaner way is to use: Rows.Count instead as this will alter based on the version of Excel you are using
4. Using this knowledge, I can set the cell to the right of it equal to the share price

Download sheet to practise how to Find The Last Cell in VBA

Training Video on how to Find The Last Cell in VBA: