Removing blanks where blanks show blank but are code to show blank.

Hi Guys
I have the following code which was kindly given to me by a colleague at ExcelForum. It was designed to remove one row if that row was empty which it does. I have tried to use it in another location where there are several hundred blank rows which it was not designed for and takes a very long time removing some. Eventually having moved some it gives up.
Firstly I can’t work out why it is removing some as they appear identical to the ones not removed
Secondly it is very slow.
So is there any code that would remove these blanks.
Here is the code I have

Please Login or Register  to view this content.

The problem is that I have now run a macro to pull from another sheet the data I need. The macro shows “ “ in the cells I do not need but behind that there is the code. I do not know how Excel recognises this. If anyone has a suggestion I would be thrilled.
I am uploading the workbook which puts my comments into context. The sheet I am working in is Petty cash and the data is being pulled from BDC2. The macro (4) has just been run. There are entries in the petty cash sheet. Some of the columns are hidden because there are not needed.

Thanks in advance
Mark

Hi mark

Try this link

http://www.ozgrid.com/VBA/VBACode.htm

Well in your workbook code, the bit that you have put in the post that deletes the empty rows has been commented out? I'd suggest you need to step through backwards though otherwise it will skip rows:

Please Login or Register  to view this content.

Hi Yudlugar and Sktneer

Youlugar I am not sure where to enter this code. I have played about with it to no avail.

And Sktneer I copied you code into my VBA and ran it. Nothing happened.

Still trying to see how to use either one.

Mark

I had gone through all the macros on this sheet before posting my question. Still do not have the solution. I am sure it is somewhere but where? Thanks.

Mark, your "Macro4" should be:

Please Login or Register  to view this content.

This worked for me:-

Please Login or Register  to view this content.

Hi Sktneer
I must have copied something incorrectly or other. I have run yours as above and its great. I don't know how to apologise enough as the time and effort given by you is so appreciated. I will now go back and see where I went wrong. I am learning every time I do something but it is not recognisable to me yet to spot where i have done something incorrect.

But thank you very very much

All the Best
Mark

Hi Yudlugar

Thanks again for a delightful piece of code. Don't know how long it is going to take me till I am able to write code which does what it should. I will keep studying what you have given me and see how much of it I can understand. I may have committed a most cardinal sin in that I had not tested correctly the code given to me by Sktneer which actually worked. By studying that also there is a hope I will improve.

All the Best Mark

Mark,

Not a problem - few pointers:

-I think one of the reasons your original code was running very slowly was that everytime you deleted a line of code the entire sheet recalculated itself. This takes a bit of time when you have so many formulas in the sheet.

-Your original code assigned the same r1c1 formula to several columns individually, this was unneccessary, you could do it all in one go.

-When deleting rows, you need to loop backwards otherwise you will skip some.