Copy all cells except blanks, keeping the same spacing.

Hi guys,

I've looked around the web and cant find a solution to my problem.
What I want is similar to copy and paste, except it doesn't copy over existing cells in the final range.

I'll provide an example:
the following cells are
A1 = stringA
A2 = empty
A3 = stringB

B1 = 4
B2 = B1*B3
B3 = 5

resulting in:
B1 = stringA
B2 = B1*B3
B3 = stringB


Ideally I would want this to work for any sized range (rangeA and range B always the same column and row dimensions however of any size), and copying from any part of the sheet to another.

Any help would be great,
Thanks.
Jimmy

So what is the rule - I do not get it from your example
A1 string, B1 number - required result = string from A
A3 string, B3 number - required result = number from B

Going back to post#1

A1 = stringA, Not blank, copied to B1, and B1 = stringA
Following same logic
A3 = stringB, Not blank, so B3 = stringB

But - post#1 says B3 result = 5
Why?

Will the range selected always be a single column?

If not, if range S10 to W23 is selected, where do the values go?

You should use a formula in an empty column.

Paste this formula in cell C1 and fill down.

Formula:

Please Login or Register  to view this content.

Copy Column C and Paste Special values into cell B1.

Select Column B.

Select the Data Tab, Select Text to Columns and click on finish.

Job Done.

As a Macro:-

Please Login or Register  to view this content.

If it was just one column and you want it in the right click menu (the point of the first section) this is a start
(note I am creating the reference that you may or may not have)

in the thisworkbook module

Please Login or Register  to view this content.

and this in the normal module

Please Login or Register  to view this content.

Ahh i see your answer was that it was a range in that case i'd need to split by vbtab as well... not sure the efficient way to do that.

deleted by kev - see post#12

Select cells to copy and run a macro
Test in attached workbook with shortcut

This is the normal copy and paste - cell references are re-referenced: shortcut {CTRL} k

Please Login or Register  to view this content.

This literally copies whatever is in each cell - cell references do not change: shortcut {CTRL} {SHIFT} k

Please Login or Register  to view this content.

This pastes values: shortcut {CTRL} {SHIFT} v

Please Login or Register  to view this content.