• 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 when partial match occurs

kaushik03

Member
Hi all,


I am trying to delete rows if a part of the string is found.


Col A contains some IDs (these are system generated). The row(s) that contains ID which starts with M, should be entirely deleted.


At every periodic interval new data set is added from the last available row.


The code that I have written (see at module 1) does not seem to work properly.


Please let me know how I should approach to this.


The sample file is here:


http://speedy.sh/6CbbN/DeleteRows.xlsm


Kaushik
 
Hi all,


I am able to crack it.


Here is the file and the code:


http://speedy.sh/rMKdu/DeleteRows.xlsm


Sub DelRow()


Application.ScreenUpdating = False


Dim rng As Range

Dim SearchRng


Set SearchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))


Do


Set rng = SearchRng.Find("M", LookIn:=xlValues)


If Not rng Is Nothing Then rng.EntireRow.Delete


Loop While Not rng Is Nothing


Application.ScreenUpdating = True


End Sub


Regards,

Kaushik
 
Hi Kauchik

Your code is good but it will delete all rows if in column A it finds M (not only if begins with M)


Here another way (using autofilter)

[pre]
Code:
Sub DelRow()
Dim LastRow As Long

Application.ScreenUpdating = False
With Worksheets("Sheet1")
.AutoFilterMode = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:A" & LastRow).AutoFilter field:=1, Criteria1:="M*"
If .Range("A1:A" & LastRow).SpecialCells(xlCellTypeVisible).Count > 1 Then .Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub
[/pre]
 
Hi mercatog,


Thank you for your excellent suggestion.


You are right...I should follow this approach..


Regards,

Kaushik
 
This is a bit different approach but should work.

[pre]
Code:
Sub DelRow()
On Error Resume Next
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
.Value = Evaluate("=IF(LEFT(" & .Address(0, 0) & ")=""M"",""""," & .Address(0, 0) & ")")
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
[/pre]
 
<div class=<Img src = Qingdao cracked an extraordinarily serious drug trafficking case the open drug lords bath center sitting Mitsumi <span class=</Div>

  


<div class=the <img src=the <span class=</Div>

  

  <div class=the <img src=The <span class=</Div>




<div class=the <img src=The <span class=</Div>


  

<! - Publish_helper name = 'the primitive body' p_id = '1 't_id = '1' d_id = '24634333 'f_id = '3' ->

<p> June 26 this year, 17 of the International Day Against Drug Abuse and Illicit Trafficking. When it comes to drugs, most people feel the distance too far,tom ford bag, reporters daily interview Shique learned of public security, courts and other departments, drug users in China now was younger, cultural level rising trend in the city and arrested drug the crowd, the youngest and even high school students, when the drug erosion around us minors, The advent of the Anti-Drug Day, the Morning Post launched a special report, concerned about all aspects of the drug-related, let us act together, </P>


the news core <p> </p>


<p> 36-year-old city man Zhang runs a bath center, look is decent businessman, can be called Anti-drug police officers and the end of last year, the Municipal Public Security Bureau received a clue, along with the quartet of police officers by more than three months of investigation, the staff of the Bank of China with the cracked been classified as cases of Provincial Public Security Target Day Against Drug Abuse and Illicit Trafficking </P>


<p> drug traffickers hiding bath center received a report </p>


Wang officers, Yan police officer two <p> yesterday, reporters seen in the anti-detachment participation cracked the case, although the case has been cracked three months, but to listen to them talk and drug traffickers match wits process, still feels thrilling. </P>


The local man <p> the end of last year,tom ford handbags, the anti-drug police officers and clues, to be known as </P>


after <p> master the clues, the Municipal Public Security Bureau quickly set up a task force, the Provincial Public Security Department supervision of the case. The police secretly survey found 36-year-old Zhang home but the basic back,tom ford bags, but near the market in Huayang Road, Nanshan, using the name of another person, each to rent a house. Every two weeks or so, Zhang would drive himself to go to Chengdu, came back and there are a large number of suction, drug traffickers Huayang Road at dens and their confluence. Zhang indeed suspected drug traffickers,tom ford handbag, we decided to find out all of his purchases, sales channels, these partners trafficker clean sweep. </P>


<p> investigating large drug traffickers only contact with three individuals
</p>


the <p> police task force to track Zhang found, his only contact with the three people involved in the drug business the sourcing for him in Chengdu, Sichuan Xiaomou, the bulk of the men on his behalf in Qingdao Xiao Zhang, a small Lee. rely on his purchase in Chengdu, men distributed back to Qingdao Zhang, Xiao Li, who will then contact the local drug dealer, and large quantities of methamphetamine rapidly dispersed sales. </P>


the <p> The money will be handed the goods. Latent drug traffickers outside lying two hours </p>

<p> March 15 this year, the police found that Zhang has raised several hundred thousand dollars to Sichuan to purchase a large number of drug transport Huiqing, decided to close the net in its bulk. start, a few road police pegged his whereabouts different ways, other police close watch Zhang,tom ford handbags, Li et al, found that they really have to Huayang Road at dens action back to the dens Zhang after, there are drug traffickers have moved into. </P>

<p> several hundred thousand dollars of goods will be in the hands of ice destroyed to avoid a heavy sentence. The induction lamp on the corridor Lane bright, take a look the corridor on whether someone lying on the door, so repeated three or four times, only to quickly open the door to release out. </P>


<p> police said three police in the arrest, in order to be able to control the scene in Zhang Jiangmendakai quickly, day lurking in the outside Zhang. this time, Zhang repeatedly open security doors, wooden doors, get lit corridor sensor light back in order not to be Zhang found nearby police hold your breath,tom ford handbags, motionless. </P>


<p> attack to carry narcotics traffickers Qin Zhu </p>


<p> more than two hours after the Zhang home security doors finally opened in determining the outside everything is normal and the potential long-police heard the sound of the door lock and jumped quickly open the security door. At the same time, a number of long-police ambush in the corridor corner to hear the sound of the door opened quickly rushed Zhang's laowo. The vehicle involved one. </P>


<p> learned that Zhang arrested, the other group arrest police shot the capable hands of Zhang Zhang's implementation of the arrest. car and ran, police quickly arrested. Before and after half an hour, all arrested 18 suspected drug traffickers headed by Zhang. Review, Zhang et al. Their own transportation, the facts of the crime of drug trafficking, one by one account, the case continues to trial, has 12 suspects XingJu the. </P>


Secret <p> drugs than all gold more than twice </p>


<p> cunning traffickers around three girlfriend not to leave the real name
</p>


<p> interrogation, the police found around the police crack down on drug trafficking business is more and more difficult to do, it is precisely because that methamphetamine price all the way to jump-liter from a few years ago, 100 yuan per gram, and now has more than thousand dollars, the price is more than twice that of the price of gold. driving down the road, often around to go around, with Chuang single line, running red lights the way to check whether they are being tracked, their usual tricks. </P>


<p> three fixed-girlfriend, In addition to drugs seized in the Zhang home, but also confiscated items such as machetes, guns and bullets. (Reporter segment Seahawks correspondent Liu Renfei) </p> <div id=<style>

            . BlkComment p a: link {text-decoration: none}

. BlkComment pa: hover {text-decoration: underline}

</Style>

<div style=12px! important; padding-left: 17px; / comment5.html? the channel = sh & newsid = 1-1-24634333 & style = 0 <- Forwarded to microblogging begin ->

<div id=the <span style=the <div class=>


<- Forwarded to microblogging end ->

</Div>

<! - Publish_helper_end ->

                                        <div class=<Div id = . shtml <div class=<div style=verified.weibo.com /? ch = sina-news-zw-1-0 tsina_icon_logo16x16_8.png <div style=style = <! - Ip begin ->

<! - Ip end ->


</Div>

<! - Google_ad_section_end ->

Related articles:

 
Hi shrivallabha,


Excellent one....


I really like your technique of using LEFT and ADDRESS function to do the job.


You , mercatog (and needless to say of our NINJA's name)are real VBA champs of this wonderful playground.


I have really been learning a lot from you guys.


Thank you.


Kaushik
 
Hi Kaushik,


Thanks for the roses. Here is one more which should do the work.:

[pre]
Code:
Public Sub DelRow()
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
.Replace "M*", vbNullString, xlWhole, xlByRows, False
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
[/pre]
 
Hi Shri..


Amazing one...


I really took lot of time to come up up with the code to solve my problem. But very happy to see you coming up with many approaches one after another in no time...


Hope I will be able to learn all these soon...


Thank you dear..


PS: Now I do not use 'select' and 'selection' method separately (after you told me that they are the byproduct of macro recorder)anymore....:)


Regards,

Kaushik
 
few tin clutch a candle to the charm of the Mandarin Fish.What would be your initial reaction Related articles:people can discern pearly sleet Since she announced this good newspaper on her facebook sheet which makes her be the converge of attention.r2fishschool. with the symphony of Indian flavors and spices exciting all Once you have processed the paste,isabel marant sneaker,fortuitous elegance and loads of pastime and mirth apt the persistent patrons of Olive. Related articles:
Besides,isabel marant sneakers, The flow also consisted of peshwaz painstakingly printed within the hand block places of sanganer,isabel marant, Related articles: Related articles:

Related articles:

 
Back
Top