Copy a cell range whose size is likely to change from time to time (vba)

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

You are here: Home >> Excel >> vba >> Copy a cell range whose size is likely to change…

In this blog post I will demonstrate some vba copying techniques.

Example 1,

This example code copies cell range A1:B2 to A5:B6 in the active sheet. You can make the code even shorter: Range("A1:B2").Copy Range("A5").

Example 2,

The current region is a cell range surrounded by blank rows and columns. This is equivalent to Ctrl + a.

This works very well if the cell range doesn´t have blank rows or columns.

Example 3,

Cell range A1:B6 has blank cells in row 4.

Example 4,

This cell range (A1:B6) has a blank row (4) and a blank cell (A6).

Download excel 2007 *.xlsm file

copying techniques.xlsm

Functions:

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

VBA:

Worksheet Range Property

Related blog posts