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

'This will remove the blank row only if there is one
'Sub checkTwoCols()
'Option Explicit

    Dim cel As Range, rng As Range
     Set rng = Range("A2", Range("A65536").End(xlUp))
      For Each cel In rng
        If cel.Value = "" Then
            If cel.Offset(, 2).Value = "" Then
               cel.EntireRow.Delete
            End If
       End If
    Next cel
End Sub

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

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.

This worked for me:-

Sub Macro3()

Range("A1:" & Selection.SpecialCells(xlCellTypeLastCell).Address).Select

Dim i As Long
        'We turn off calculation and screenupdating to speed up the macro.

        With Application
                .Calculation = xlCalculationManual
                .ScreenUpdating = False

        'We work backwards because we are deleting rows.

        For i = Selection.Rows.Count To 1 Step -1

                If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
                        Selection.Rows(i).EntireRow.Delete
                End If
        Next i

                .Calculation = xlCalculationAutomatic

                .ScreenUpdating = True
        End With
        
        Range("A1").Select
        
End Sub

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:

for count = Range("A" & rows.count).end(xlup).row to 1 step -1
   If Range("A" & count) = "" then rows(count).delete
next

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

Mark, your "Macro4" should be:

Sub Macro4()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Petty Cash").Range("A2:Q" & Sheets("BDC2").Range("C" & Rows.Count).End(xlUp).Row).FormulaR1C1 = "=IF('BDC2'!RC16= ""petty cash"",'BDC2'!RC, """")"
Application.Calculate
For Count = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
   If Range("A" & Count) = "" And Range("C" & Count) = "" Then Rows(Count).Delete
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

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

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

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.