Updating active cells in vba

Rated 4.89/5 based on 571 customer reviews

For the rest of this post I will use the code name to reference the worksheet.

The following code shows the above example using the code name of the worksheet i.e.

We will first attempt to do this without using Offset. If the number of days in increased then we do not need to add any more code. Below are some more examples of using Offset Current Region returns a range of all the adjacent cells to the given range.

updating active cells in vba-62

updating active cells in vba-74

updating active cells in vba-58

updating active cells in vba-78

You may be wondering what is the difference between Value, Value2 and the default: as it will always return the actual cell value(see this Stack Overflow answer.) The worksheet has a Range property which you can use to access cells in VBA.

If the Day columns in the above example were random then we could not use Offset. One thing to keep in mind is that Offset retains the size of the range. I have added borders to make the current regions clear.

A row or column of blank cells signifies the end of a current region.

This post covers everything you need to know about using Cells and Ranges in VBA.

You can read it from start to finish as it is laid out in a logical order.

Leave a Reply