• 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.

Delete Rows after certain text

T

Tanuj1587

New Member
Hello All,


My file is like

Sr.NO Name Title

1----------

2---------

3----

4

5

End


now I want to find word END and want delete all the rows below.I want it for printing.
 
Hi, Adapte this code to your sheet

[pre]
Code: 
Sub DeleteRows()
Dim LastLig As Long
Dim c As Range

Application.ScreenUpdating = False
With Worksheets("Sheet1")                          'Adapte to your sheet name
LastLig = .Cells(.Rows.Count, "A").End(xlUp).Row
Set c = .Range("A1:A" & LastLig).Find("END", LookIn:=xlValues, lookat:=xlPart)    'or xlwhole in place of xlpart
If Not c Is Nothing Then
If c.Row < LastLig Then .Rows(c.Row + 1 & ":" & LastLig).Delete
Set c = Nothing
End If
End With
End Sub
[/pre]
 
Hi Mercatog,


Just wanted to check with you if we can use the folllowing approach as well.


Sub RowDel()


I = Application.WorksheetFunction.Match("end", Sheets("sheet1").Range("A:A"), 0) + 1


Range("A" & I & ":A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).EntireRow.Delete


End Sub


Note: The code will delete all the rows after it finds the word "END".


I was just wondering, even if we have multiple 'END" at different positions in between Col A, all the rows should deleted after first occurance of "END".If my

assumtion/understanding is correct then we can use the above code without creating any loop right? I might be wrong.....


What do you think Tanuj1587? But till the time Mercatog comments on this, please use his code because he is our one of the VBA champs of this forum. I just wanted to check/validate my thought with him.


Regards,

Kaushik
 
Hi kaushik!


From a functional standpoint, both mercatog's and your code appear to work very similar. The only difference is how they handle "errors". Using the Find method, if the word "end" is not found, then all that happens is
Code: 
c = nothing. On the other hand, the [code]WorksheetFunction.Match will cause an error. Note that neither code uses a loop.


So, if everything is good and the word exists, both codes find the first instance of "end" and delete all rows below, and they would be considered equal. 


One little note, do be careful about this

[pre]Range("A" & I & ":A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).EntireRow.Delete
[/pre]
The first Range object doesn't have a parent call-out. While we might assume that Sheet1 is the active sheet and we'll be okay, it's not necessarily true. If the current sheet is Sheet2, your code operates as if you had written this:

Sheet2.Range("A" & I & ":A" & Sheet1.Range("A" & Sheet2.Rows.Count).End(xlUp).Row).EntireRow.Delete[/code]

Which would cause very different results.
 
Hi Luke,


Thank you very much...this is what I wanted to know...you have guided me in the right direction. Now I have understood the difference between Marcatog's code and mine.


I truly admit that this is the best place to learn excel and VBA, at least for the beginners like me...:) because we have such wonderful mentors like you....


Thank you again..


Regards,

Kaushik
 
Hi kaushik03, Luke M

@ kaushik03

"he is our one of the VBA champs of this forum"

That's a very big word. No I'm still begginer and each day a learn something new


Hope that Luke M analysis was clear.


Regards
 
Thanks Kaushik. I agree, that's one of my favorite things about this community. Too many of the other forums I've participated in seem daunting and overwhelming, while Chandoo has more of a friendly, "coffee-house" atmosphere where people who love XL can just get together and talk about things.


I can also remember what it was like when I was first learning all this stuff, and am grateful for the opportunity to pass along what I've learned, either from other people or through my own struggles. That lesson about missing parent objects? Been there, done that. =P
 
@Luke...


Absolutely...


Let me tell you one truth.


About five six months back I did not know how to use basic excel formulae and VBA was way beyond my dream.


But just because I am in touch with this forum for last couple of months, more precisely with such wonderful knowledgeble and highly skilled people, now I have learnt something about excel. At least basic excel formula,how to nest the formulae together and many good things/trics.


I know this is just a drop of ocean but at least a river for me:)


@Mercatog:


Yes, Luke's explanation is clear to me.


But I truly mean that you are one of our VBA champs of this forum. Because I very closely visit thuis forum.


Regards,

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