11. VBA Tips - Find The Last Cell
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 ?
- 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!)
- Why 65000 ?... I use that as it's near the limit of row 65536 in Excel 2003, and easier to type than 65536
- A cleaner way is to use: Rows.Count instead as this will alter based on the version of Excel you are using
- Using this knowledge, I can set the cell to the right of it equal to the share price
Training Video on how to Find The Last Cell in VBA: