Working with Different-Size Excel Ranges

The copy and paste functions in Excel are perhaps the most

widely used functions, used to replicate data and formulae across ranges in the

same worksheet, worksheets across the same workbook and across workbooks.

However, many a time, due to paucity of data, data has to be entered in bursts,

leaving rows blank.  If these data gaps do not get filled up at a later

stage, one may want to copy the non-blank cells to a different range in a manner

that the data appears together.  This could actually be useful in reducing

the range, which one has to scroll through in the worksheet.  This article

explains the procedure to copy non-blank cells to a different range both

programmatically and non- programmatically.

Static pasting



The range A1:A9 contains the original list of numbers and C1:C9 is the result of
static pasting.  To copy the non-blank cells in the range (A1:A9) to column

C.  Select the range A1:A9 and go to Edit>Go To>Special. Now select

Constants or Formulae (as the case may be) to select the non-blank cells in the

region. Next go to Edit>Copy, select C1, go to Edit>Paste Special, select

Values and click on OK. The problem with this method is that if a new value is

inserted in cell A3, we do not see the change in column C.

Original range is A1:A9. Cells have been left blank as information isn't available	Go to Edit>Go To>Special. Now select the Constants radio button
For the 'Paste Special' menu, click on the Values radio button and click on OK	The values get pasted in range C1:C6 without the blank rows. It's non-dynamic

Filtering the list



There is yet another procedure for displaying the non-blank cells only.

However, this procedure is not entirely dynamic



because to revert to the original list, one has to 'Select All' in the

drop-down box in cell A1. For this, cut and paste the range to A2:A10, type in

'Numbers' in A1, select the range A1:A10, go to Data>Filter>Auto

Filter.  A drop down arrow will appear in cell A1. Now click on the

drop-down box in cell A1 and select 'Non Blanks'.

To Filter data, we should give a heading to the list. It is always a good practice to clearly identify data columns	Use the Autofilter feature available in the Filter options. This will insert a drop down box in cell A1
Select the 'Non Blanks' option under in the drop-down menu available in cell A1	The list automatically displays the non-blank cells in the region. The blue row number indicates hidden rows there

Dynamic pasting



The first task is to determine the row number of non-blank cells in the

range A1:A9.  This is done by using the following formula in cell B1:

“=IF(A1="","", ROW())”.  Copy this down till cell

B9.  For blank cells in range A1:A9, a blank appears in range B1:B9. Now to

get rid of these blanks cells, we employ the SMALL() function.  In cell C1,

enter the formula: “=SMALL($B$1:$B9,1)”.  Copy it down to C9 but

increment the second argument of the small function by 1 each time-to avoid

this, you may type numbers 1—9 in a different range (say range F1:F9) and the

second argument of the small function may draw a reference to that.  To

avoid error values from appearing in range C1:C9,  use the ISERROR() and

the revised formula becomes: “=IF(ISERROR(SMALL ($B$1:$B9,1)),””,

SMALL($B$1:$B9,1))



C1 to C9 will now have the row numbers of the non-blank cells arranged

consecutively in range A1:A9. To return the non-blank cells in range D1:D9, use

the INDEX function.  The formula in cells D1 to D9 after including the

ISERROR function will be: =IF(ISERROR(INDEX($A$1:$A$9,C1,1)),””,

INDEX($A$1:$A$9,C1,1)).

Now changes in A3 are reflected in D1:D9.  The

completed formula in column



D is as follows: “=IF(ISERROR(INDEX ($A$1:$A$9,SMALL($B$1:$B9,F1),1)),

””,INDEX($A$1:$A$9,SMALL($B$1:$B9,F1),1))

D1:D6 shows non-blank cells got programmatically. Any data, changed or entered in the range A1:A9, will reflect range	The formula looks difficult to use initially, but with some practice and knowledge of functions, one can get comfortable

Ashish Mathur, Business Analyst, Pulsar Knowledge Center