I look forward to reading about your experiences.
![api vba range.cells() api vba range.cells()](https://i.stack.imgur.com/DvINc.jpg)
Let me know in the comments section below how you use resizing a range in your macro code! Also, if you can think of any other ways to use VBA code to resize your ranges automatically, post your coding method in the comments section so we can improve the current list. There are many, many more examples of this and I'm am sure you can think of a few examples yourself. There are a ton of scenarios that may require you to have an automatically expanding and collapsing range reference. Manually you can do this by saving your spreadsheet or you can use the command shown in the below code to refresh with VBA. It is vital that you refresh (recalculate) the UsedRange before using it as the method will pick up ghost cells (cells that had values in them and you used the Delete key to remove the values). The UsedRange method creates a range that includes every cell that contains data in it on the spreadsheet.
![api vba range.cells() api vba range.cells()](https://www.superexcelvba.com/static/images/en/tutorial/example-cells.png)
Through lots of trial and error, I've come up with a list of 5 different methods you can use to turn your static range references into powerful, auto-adjusting machines! All of these code snippets have advantages and disadvantages, so make sure you pick the one that works for your particular situation. This meant I didn't have to go into my macros and change range addresses in my code every time the size of my data changed (which was typically every day).
#API VBA RANGE.CELLS() HOW TO#
A huge turning point for me when I was teaching myself how to write VBA was being able to create dynamic code.