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

Worksheet Rows Property

Range.CurrentRegion Property

End Property