• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare 2 columns & that based delete all extra rows above till heading

C

Chirag R Raval

Member
Dear Sir,

I have database sorted on first columns "K"
& then sorted on column "I" ...both ascending.....

if first found in "K" >0, this is start point ...now code should check just above
row but in column "I" ....& Search above till last found value >0 in this column

I just want to delete all rows of database till last found value >0 in this "I" (heading should remain)

I attached sample file with
(1) yellow highlighted for start point
(2) green highlighted start point for search above & also should be remains those rows.
(3) just data found "0" , this "0" and all above rows I want to delete...till heading...

can anyone help?

Regards
Chirag Raval
 

Attachments

  • SAMPLE DATABASE-2-COLUMNS COMPARE & DELET EXTRA ROWS ABOVE .xlsx
    47.6 KB · Views: 8
Chirag R Raval
1) Have a backup
2) Verify that You have a backup
3) Run next code
Code: 
Sub DelSomeRows()
    Application.ScreenUpdating = False
    With ActiveSheet
        If .FilterMode Then .ShowAllData
        y_max = .Cells(.Rows.Count, 1).End(xlUp).Row
        If y_max = 1 Then Exit Sub
        .Range("A1:W" & y_max).AutoFilter Field:=11, Criteria1:="0"
        .Range("A1:W" & y_max).AutoFilter Field:=9, Criteria1:="0"
        .Rows("2:" & y_max).Delete Shift:=xlUp
        Selection.AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
 
Dear Sir,

I declare Dim y_max as long...

Great...One Click & in sheet...stay data which require only ...

amazing ...many thanks...

I also try to filter but can not ...do next steps ..can not set
condition in code ...which you apply like a simple logical magic.

Thanks again to you & all members
Thanks to this organisation.. & hope my post can be helpful
for others to resolve their excel VBA problems ..

I want to mention here (if any one no problem)
my previous conversation about with Mr Vletm's
at http://chandoo.org/forum/conversations/auto-advance-filter-and-so-on.3424/
though Mr Vletm's answering style is seem hard to users ,
but "Guru"s always in good think for their pupils ...that
user should try till they can.....& finally ask to "Gurus" ....
if we don't try to solve till we can...there are no meaning for us
to take ready made solutions...readymade actually -really harm to our Learning capacity & make us dull to struggling with our problems..

thanks again for Mr Vletm..for give us forever useful little hint..

Regards,
Chirag Raval
 
Chirag R Raval
1) I also try to filter but can not ...do next steps ..can not set
condition in code ...which you apply like a simple logical magic.
Did You tried to modify code? ... that cody should be as You needed!
If something won't work ... then You could always ask more details.
Backups have to have before 'Deleting' something in the 1st time.
2) about that 'conversation':
These needs to make small steps.
If basic things aren't ready and sure that those works then
... even small 'thing' can take forever and too many testings.
> Okay <
 
Dear Sir,

you are right..I really needed to introduce with my tried code

I tried to record macro for filter...give first criteria >0,on column "K", & then
2nd filter on >0 on columns "I"..its okay.... but i stop at how can I delete area which not visible? then i confused & want to asked in this forum ...without code .

I think (actually feel fear) that if I represent recorded code ...& if real method will be different..then whole thread stop at my tried code... & any one who want help..me ,..just try to modify my code other than really perfect method...

I confused there... so I decide not to put that code...

& you help me like a angel..

basic thing i realised that you really awaken my soul....from now...even when I confuse somewhere in code I present with my tried code which may become starting code for get solution. Or for find some way towards solution..
if it wrong or right ..but tried...starting is necessary...for get solution...

many thanks for your guideline..

Regards,
Chirag Raval
 
Chirag R Raval
So You didn't try that code or how?
After few lines,
it makes those two filtering as You wanted - okay?
Next line Deletes all visible rows from that range I named.
... You should filter = to see,
those rows with which You would like to work - not those which You don't!
It's same, if You go to shop and You would like to buy apples ...
where do You go in the shop? Near shoes, CDs, drinks or apples? ... maybe apples?
Maybe that was something what is missing from Your code...?
> No need to fear to make questions! There are not 'st..id' questions. There are just 'st..id' answers! <
 
Dear Experts,

problem occurred...

There are need to "Resume Next Statement" instead of "Exit Sub"
please refer my attached example file,...

Row No 9 to up to just below heading, if there are not found "0" in column "I"
(after values end (from bottom up) & "0" start)

macro delete all the rows ..just heading remains..

there are need to remains rows "if "Column "I" goes towards Bottom to Up" till values >0, if not found "0" then nothing to do ..just resume on next process of macro. (should not delete anything)

can any one help to modify this macro?
There are need to "Resume Next Statement" instead of "Exit Sub"

Regards
Chirag Raval
 
Dear Sir,

Many Thanks ...

Its in your only post no 2709 of this thread (above) ...
please refer from first to understand my problem...also request to refer same attached file .

please refer your code...

problem is..
.assume there are not 0 value rows in red area...in column "I" ,
this code delete all data below heading ...

requirement is ... if not any row found with value "0"...nothing need to do. Just resume next process of hole macro...

Regards,
Chirag Raval
 
Chirag R Raval
with this code You can run same macro many times...
Code: 
Sub DelSomeRows()
    Application.ScreenUpdating = False
    With ActiveSheet
        If .FilterMode Then .ShowAllData
        y_max = .Cells(.Rows.Count, 1).End(xlUp).Row
        If y_max = 1 Then Exit Sub
        .Range("A1:W" & y_max).AutoFilter Field:=11, Criteria1:="0"
        .Range("A1:W" & y_max).AutoFilter Field:=9, Criteria1:="0"
        y_max = .Cells(.Rows.Count, 1).End(xlUp).Row
        If y_max > 1 Then Rows("2:" & y_max).Delete Shift:=xlUp
        Selection.AutoFilter
    End With
    Application.ScreenUpdating = True
End Sub
 
Dear sir,
Thank you very much for your help..
I am now on home...No pc to test..
I will be back tomorrow ..with test result..
Again thanks..

Regards,

Chirag Raval..
 
You must log in or register to reply here.
Back
Top