Delete blank rows between blocks of data except two blank rows

[YasserKhalil]

Hello everyone
I have a range between columns C and H and in column E there is data and inbetween data there are different number of blank rows
How can I remove the blank rows (between C and H only) and at the same time leave two blank rows

I have put before and after ..to make it clear
Thanks advanced for help

[Trebor76]

Hi Yasser,

Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):

Please Login or Register  to view this content.

Regards,

Robert

[Marc L]

How can I remove the blank rows (between C and H only) and at the same time leave two blank rows

A classic EZ way :

Sub Demo1() 
            Dim Rg As Range, Rf As Range, DZ& 
    With Sheet1.[B4].CurrentRegion.Offset(, 1).Resize(, 6).Rows 
            Set Rg = .Parent.Cells(.Count + 4, 5).End(xlUp).CurrentRegion 
            Set Rf = Rg.End(xlUp) 
       Do Until Rf.Row < 4 
                DZ = Rg.Row - Rf.Row - 3 
             If DZ > 0 Then .Item(Rf.Row - 2).Resize(DZ).Delete xlShiftUp 
            Set Rg = Rf.CurrentRegion 
            Set Rf = Rg.End(xlUp) 
       Loop 
    End With 
            Set Rg = Nothing:  Set Rf = Nothing 
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[YasserKhalil]

Thanks a lot for replies ..
@Mr. MarcL
I am lost in fact .. How can I edit your code to work with columns E ...regardless relying on column B current region ..
And in this loop I think rows will be delete one by one ... I have about 50,000 rows so I think this will be slow

[YasserKhalil]

Thanks a lot Mr. Trebor for your working solution
Thank you very much

[Marc L]

Yasser,

thanks for the rep' !

Did you try my code with your sample workbook joined ?
As it rocks on my side so already working with column E ! (Just check Rf & Rg address …)
And rows are not deleted one by one but by blocks as you can see within my code !

Of course as a demonstration, you can mod it in order to work faster by desactivating display (via ScreenUpdating)
or rewrite the engine via a formula and a sort as you already saw recently in a previous thread …

The code was made upon informations given in your initial post so we couldn't guess those 50 000 rows !

The better initial post, the better code …

[xladept]

Hi Yasser,

I wrote one too

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot my friend
I appreciate your solution a lot ..

[Marc L]

The fast very EZ beginner way, formula at child level :

Sub Demo2()
         Application.ScreenUpdating = False
    With Sheet1.Range("C4:H" & Split(Sheet1.UsedRange.Address, "$")(4))
         With .Columns(6):  .Formula = "=AND(E4=0,E2=0)":  .Formula = .Value:  End With
        .Sort .Cells(6), xlAscending, Header:=xlNo
        .Columns(6).ClearContents
    End With
         Application.ScreenUpdating = True
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[YasserKhalil]

Yes Mr. Marc
That's the best (child level is the best approach) but we can't think as children as they are more creative than adults
Best Regards

[Marc L]

In fact it's the manual way when directly operating within worksheet, faster than creating any code !

[xladept]

You're welcome old friend and thanks for the rep!

[Trebor76]

Thanks for my rep too

[Marc L]

The same, thanks again Yasser for the new rep' !