Delete empty rows

[Ismael]

Hi to all,



I have 2 questions regarding the file attached.



So the first one is how can I delete empty rows automatically for example whit a macro.

I already see one post of the forum that talk about this question but doesn?t work in my case, and I try to adapt to my question but without success.



The second question is:



In the attached file you guys can see that in the column B I have a cell that contain the word ?Data? for example cell B4, B31, 56, etc.

What I pretend is every time that I find this word I want to put in right cell (for example C4, C31, C56, etc) the value that is in the cell C3, C30, C56, etc.



I don?t now if you guys will understand this doubt, but if you have any doubt please let me now ok?



Best regards,



Ismael

[Bob Phillips]

Select cells B4 down to the end of the list
Menu Data>Filter>Autofilter
Click the arrow in B4
Select Blanks from the bottom of the list
Delete all the rows where the row number is blue
Menu Data>Filter>Autofilter
Done!

Problem with a formula for the second bit is that it will overwite C30 unless you just insert in C4, C31, etc. which seems self-defeating.

[Ismael]

Hi,

In first place thanks for the help.

I just don't full understand what you said regardaing the second bit, can you give me one explanation in more detail.

thanks

Ismael

[Bob Phillips]

Hi,

In first place thanks for the help.

I just don't full understand what you said regardaing the second bit, can you give me one explanation in more detail.

What I mean is that could put a formula in say C4. But you would want to copy it down which mean s that you would overwrite the values in those cells, which is hard as that is where you will get the value when the B column = Data.

[Ismael]

Hi,

sorry my mistake, I don't explain well what I pretend.
So in the file attach, in the green cells you will see that for each "group" of data I repeat the number that is in cell C3, C30, etc.

Is that what I pretend to do in one automatically way, because the original file is very large to do this manually.

I hope that now you can help me.

best regards,

Ismael

[Ismael]

Hi,

Problem solved. I search in another forum (MrExcel) and I find a formula that after same adjust works fine in my case.

The formula is:

=IF(ISNUMBER(B5);LOOKUP(9.99999999999999E+307;$C$1:C5);"") 

as you can see in the file attach everythnig works fine now.

Thanks.

Best regards,

Ismael