Charlie, Either of these test fine to remove ** or more
Sub DeleteFilteredRowsSAS()
With Range("B5:B" & Cells(Rows.Count, 2).End(xlUp).Row)
.AutoFilter field:=1, Criteria1:="~**"
.SpecialCells(xlVisible).EntireRow.Delete
.AutoFilter
End With
End Sub
Sub delrowsSAS()
Dim i As Long
For i = Cells(Rows.Count, 2).End(xlUp).Row To 5 Step -1
If InStr(Cells(i, 2), "*") > 0 Then MsgBox i 'Rows(i).Delete
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
From: Cab Boose
Sent: Friday, April 06, 2012 6:01 PM
To: [email protected]
Subject: Re: $$Excel-Macros$$ Deleteing Rows based on single column contents
Hi Don and Asa
Yes I have just found your code. The asterix code works well. Now between the
different options I can achieve what I need. Magic.
Thanks and regards to you both.
Charlie Harris
On Sat, Apr 7, 2012 at 10:54 AM, Asa Rossoff <[email protected]> wrote:
Charlie,
Did you see the solution I posted for you?
From: [email protected] [mailto:[email protected]] On
Behalf Of Cab Boose
Sent: Friday, April 06, 2012 3:53 PM
To: [email protected]
Subject: Re: $$Excel-Macros$$ Deleteing Rows based on single column contents
Hi Don
Half way there. All the options I use with spaces etc work very well. The
one stumbling block is to delete the rows with *********
It works but deletes all rows except the header.
I see the text to find is normally inside of the two asterix like
*setouts* , but if I do with ********** it just deletes all rows.
There a 70 * in the cell
**********************************************************************
Your comments would be appreciated.
Thanks
Charlie Harris
On Sat, Apr 7, 2012 at 1:02 AM, dguillett1 <[email protected]> wrote:
Try either of these. If you want more than one incorporate AND
Sub DeleteFilteredRowsSAS()
With Range("B5:B" & Cells(Rows.Count, 2).End(xlUp).Row)
.AutoFilter field:=1, Criteria1:="*SETOUTS*"
.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
' MsgBox "hi"
.AutoFilter
End With
End Sub
Sub delrowsSAS()
Dim i As Long
For i = Cells(Rows.Count, 2).End(xlUp).Row To 5 Step -1
If InStr(Cells(i, 2), "SETOUTS") > 0 Then MsgBox i 'Rows(i).Delete
Next i
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
From: Cab Boose
Sent: Friday, April 06, 2012 3:06 AM
To: [email protected]
Subject: $$Excel-Macros$$ Deleteing Rows based on single column contents
Hi
In worbook attached I am deleting rows based on contents of column C.
When I use as it is set up at the moment it works as it should and deletes
rows with SETOUTS in them. That is good.
However, in a second similiar macro, I also want to delete rows that have in
column C this : *************** but as it is a wildcard character I
am having difficulty to get it to work. Usually deletes most of the sheet.
How do I tell in the macro that the last asterix is the wild card and that
previous asterix are what is in the cell ? and that is a row to delete.
A similiar thing is there are a lot of rows that have 6 spaces and then some
text, text varies and unknown. I have tried putting in 6 spaces and * (
*) or (* ) like that but to know available. how do you allow for
spaces preceding unknown text ?
A model railroad project.
Thanks and regards
Charlie
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to [email protected]
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to [email protected]
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to [email protected]
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to [email protected]
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to [email protected]
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to [email protected]
