• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding Blank Cells Using [GoToSpecial]command selects Non-Blank Cells (if cells are merged)

AAP

AAP

Member
Hello,
I encountered a problem. In a worksheet if I use excel inbuilt GoToSpecial feature to find blank cells it selects the blank cells + non-blanks cells if cells are merged (meaning excel understands that merged cells are blanks). I don't know how to handle this situation. Because, I want to write something in those blank cells but if it selects non-blank cells and overwrite it then it will be a disastrous. A test file is attached. Thanks for help.
 

Attachments

Khalid NGO said:
Hi,
Can you try this:
Go To > Special > Blank
Unmerge

again:
Go To > Special > Blank

Regards,
Click to expand...
Thanks a lot Khalid! But how do I make the unmerge cell merge again. My intention is not to touch those merged cells because that could be a header or display.
Regards/Amit
 
AAP said:
Thanks a lot Khalid! But how do I make the unmerge cell merge again. My intention is not to touch those merged cells because that could be a header or display.
Regards/Amit
Click to expand...

Amit I think there should be way in VBA, (i am not familiar),
What I can suggest is you can restrict the area of blank cells to be selected.
First select the range of your data, then use above procedure.

Regards,
 
Khalid NGO said:
Amit I think there should be way in VBA, (i am not familiar),
What I can suggest is you can restrict the area of blank cells to be selected.
First select the range of your data, then use above procedure.

Regards,
Click to expand...
Thanks, Khalid
Regards/Amit
 
NARAYANK991 said:
Hi Amit ,

See if you can incorporate this code in your own.

Narayan
Click to expand...

Many thanks Narayan, I have pasted my code down the one you suggested but couldn't incorporate it. Actually I also want the code should run very fast because my worksheet contains 500,000 rows and 20 columns and then excel need to find blanks and then write something. That's the reason I use excel's inbuilt function so that it works fast but I ended up with no success. Help me to find an efficient way.
KindRegards/Amit
 

Attachments

Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.
 
bobhc said:
Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.
Click to expand...


Hi Bob,

Thanks for giving me an understanding behind the merged cells. I will take care before using it.

Thanks & Regards
Amit
 
You must log in or register to reply here.
Back
Top