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
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
Hi Yasser,
Try this (though initially on a copy of your data as the results cannot be undone if they're not as expected):
Regards,
Please Login or Register to view this content.
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
Hi ! Try this ‼
Originally Posted by YasserKhalil
A classic EZ way :
PHP Code: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 » !
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
Thanks a lot Mr. Trebor for your working solution
Thank you very much
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 …
Last edited by Marc L; 03-25-2018 at 10:00 PM.
Hi Yasser,
I wrote one too
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Thanks a lot my friend
I appreciate your solution a lot ..
As yet shown …
The fast very EZ beginner way, formula at child level :
PHP Code: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 » !
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
In fact it's the manual way when directly operating within worksheet, faster than creating any code !
You're welcome old friend and thanks for the rep!
Thanks for my rep too
The same, thanks again Yasser for the new rep' !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks