Delete empty rows

Hi all,

Pls take a look at the following excel file: (- represents blank cell that may contain formulas)
Cell Cell
A B
1 -
1 -
1 -
- 1
- -
1 1

Now, I want to delete those rows which has only all the rows blank (that includes formulas that returns nothing), when I click on my mouse to the sheet (say sheet1).


Is it possible to do so?


Thanks in advance.

In Dhaka

Hi there!

Thats Correct. Thanks for asking.

1 - ......
- 1 ......
- - - - -
1 1 ......
- - 1 1 1

Only the 3rd row will be fully deleted when I click on the sheet. The blank cells may contain formulas!

ID

Hi there!

Thanks. I thought about that.

But I was trying to find a VBA solution to spare from that.

By the way, there would be two such ranges in my excel sheet. And using a for loop is not the option, as each time the cell is activated, it starts working on a new range!

BR,ID

Hi again.

Thanks a lot to both you!

Yes, you rightly pointed out, the requirements do keep growing!!

But on second thought, its not an issue! Let me try to re articulate the problem bit better this time.

If I put an additional row, thats hidden but keep tracks of whether the rows in a range are empty or not, it also takes care of cells without value but formulas buried inside (I need them to be deleted!)

By the way, pal, I tried to run the code, but things dont seem to be working! No change! Btw, I am not a veteran excel programmer. So,need more hand-holding!!

I am attaching the file if I am not clear still.

Thanks again folks!

I can feel I am getting closer to a solution.

NB. In the attachment I am just trying to delete the rows that are blank on Column A whenever I select the sheet.

Btw, if sheet activation is not possible, can I invoke a macro command using a formula?
In that case, using a filter as suggested above may solve my case.