Copy & Clear (auto)

[Median]

Been working on a basic macro to copy & clear out the "false blanks" from my data

Please Login or Register  to view this content.

However I just wondered how I could adjust the macro to:

1. Perform the code automatically if either KY1 or KZ1 gets changed
2. Only initially copy down to the cells which have values (KY2:KZ15) (i.e. dynamic & not set to 2000)

Cheers
Mdn

[Mumps1]

Place this macro in the worksheet code module. Change a value in KY1 or KZ1 and exit the cell.

Please Login or Register  to view this content.

I couldn't understand the purpose of the part of your code that clears the contents. If a cell is already blank, there is no need to "ClearContents".

[Median]

Place this macro in the worksheet code module. Change a value in KY1 or KZ1 and exit the cell.
I couldn't understand the purpose of the part of your code that clears the contents. If a cell is already blank, there is no need to "ClearContents".

The problem is it's not blank (I wish it was blank!!)

s2NpLCmgQ6eKRsZA6zbV2g.png

Column LE is looking at column LC, the blanks should be true (ISBLANK), but aren't.

The problem above is linked to an early thread (https://www.excelforum.com/excel-for...e-matches.html) & Special-K's formula only works if the cells are really blank

[Mumps1]

If column LC isn't blank, what's in it? As far as I could determine, the cells in column LC that appear blank, are blank. If I use a formula such as =LEN(LC2), it returns a length of zero meaning there is nothing in cell LC2. Did the macro I suggested work for you in copying the values of KY2:KZ15?

[Median]

If that's the case why is =ISBLANK(LC2) returning false?

W6P8DDxkSDyfzUt9LhGoCQ.png

The copy bit works great, however the purpose of the macro was to also remove the blanks (i.e. ClearContents) (i.e. atm it only does half the job)

[Median]

Here's what I need it to look like (hence why the ClearContents bit is an unsung hero!)

4r0LNqIXRGW-9nwPlMcksA.png

[Fluff13]

This will make the blanks blank

Please Login or Register  to view this content.

Change the columns to suit.
Based on this thread you started https://www.excelforum.com/excel-pro...ml#post4899819