Edit>Go To>Special>Blanks

[Kshipra]

While working with Edit>Go To>Special>Blanks, it captures Merged Cells as
blanks and deletes the values inside. Is there a way, we can control
operation on Merged Cells.

 

[Rick Rothstein]

I'm confused as to what you are doing. It appears you are selecting blank
cells and then, I'm guessing, hitting the Delete button (hence the "and
deletes the values inside" part of your post). My question is... if the
cells are already blank, why are you trying to delete their content? Perhaps
if you give us a little more detail as to what you are trying to do, then
maybe someone here will be able to come up with a solution for you. I will
note that merged cells do contain blank cells within them and the Go To
method you are using picks them up... I'm thinking you may need a VB
solution to whatever it is you are actually trying to do in order to be able
to avoid merges cells. Would that be an acceptable solution? If yes, then
you will have to give us a clear idea of what you are trying to do so we can
provide you with the code to do it.

 

[Gord Dibben]

Welcome to the wonderful world of merged cells.

A workaround for this is to select only the entire column with the leftmost
cell in the merge.

Do not select the cells only.

Assuming your merged cells encompass more than one column.


Gord Dibben MS Excel MVP

 

[Kshipra]

Hi Rick,

Thanks for your response.

Let me explain, what I am trying to do. While we import data, or work on
formatting cells, most of the time, we just select Column and apply Formats.
With this, even Blank Cells get formatted and increases file size.

What I am doing is finding all Blank Cells in worksheet at one go and
deleting them. But in a way Merged Cells also get deleted, which is a real
problem.

Is there any way, I can control this.


Regards,
Aravinda

 

[Gord Dibben]

I don't think formatting blank cells will increase the file size appreciably
but if you feel it does, why don't you select the blanks and simply clear
the formatting rather than delete?


Gord Dibben MS Excel MVP