Delete all rows that don't start with CRN or AT

Printable View

07-17-2006, 09:14 AM
Djblois

Delete all rows that don't start with CRN or AT

I want to be able to delete all rows that don't begin with AT or CRN in column A. Ex:

AT12345
AT56789
CRN12345
CRN56789
(Cust
GHI

I want it to delete the whole row that has (Cust and GHI at the beginning of the A column. This is just an example there may be much more I need it to delete that is why I want it to delete it if it doesn't begin with AT or CRN. That is a must.

I would also like it then to delete anything that starts with ATalanta. but it must have Atalanta in it not just AT.
07-17-2006, 09:32 AM
mdmackillop

How many rows are you dealing with?
07-17-2006, 09:36 AM
Djblois

Always different
07-17-2006, 09:37 AM
mdmackillop

10, 100, 60000?
07-17-2006, 09:41 AM
Djblois

Can go from 15 all the way up to 60000. This is for the macro I am doing it depends on how much info the user wants from our system.
07-17-2006, 09:44 AM
mdmackillop

Thanks,
It changes the methodology as I guess looping 60000 cells will take a while
07-17-2006, 09:59 AM
Djblois

Don't thank me, I should be thanking you. You are the one helping me. Thank you for all the help!!!!!!
07-17-2006, 10:32 AM
mdmackillop

Try this.
It writes a value "x" into column 7 and then filters on that column. You may need to change this to suit your data

Code:

Option Explicit Sub KeepSelectedRows() Dim StartToKeep, STK StartToKeep = Array("AT", "CRN") Rows(1).Insert Cells(1, 1) = "Sacrifice" 'Mark rows to keep For Each STK In StartToKeep Cells.AutoFilter Field:=1, Criteria1:=STK & "*" Intersect(ActiveSheet.UsedRange, _ Columns(1).SpecialCells(xlCellTypeVisible)).Offset(, 6) = "x" Cells.AutoFilter Next 'Delete unmarked rows Rows(1).Insert Cells(1, 1) = "Sacrifice" Cells.AutoFilter Field:=7, Criteria1:="=" Intersect(ActiveSheet.UsedRange, _ Columns(1).SpecialCells(xlCellTypeVisible)).EntireRow.Delete 'Delete ATalanta rows Cells.AutoFilter Field:=1, Criteria1:="ATalanta*" Intersect(ActiveSheet.UsedRange, _ Columns(1).SpecialCells(xlCellTypeVisible)).EntireRow.Delete Columns(7).ClearContents End Sub
07-17-2006, 11:10 AM
Djblois

Mdmack,

Can you explain to me exactly what that does? It will delete anything that doesn't begin with At or CRN??? I don't want to have to select the row at first. Also, there may be blank rows in there also that I want it to delete also (sorry, I forgot to tell you that before)
07-17-2006, 11:29 AM
mdmackillop

The code uses autofilter to find any rows starting with the strings listed in StartToKeep array and insets an X in a corresponding column. It does this for each term. It then searches that column for blanks and deletes all rows found. Finally, it searches for Atalanta, and deletes all rows found. I've amended my code in the attached example to keep blanks in Column A

Code:

Option Explicit Sub KeepSelectedRows() Dim StartToKeep, STK Application.ScreenUpdating = False StartToKeep = Array("AT*", "CRN*", "=") Rows(1).Insert Cells(1, 1) = "Sacrifice" 'Mark rows to keep For Each STK In StartToKeep Cells.AutoFilter Field:=1, Criteria1:=STK Intersect(ActiveSheet.UsedRange, _ Columns(1).SpecialCells(xlCellTypeVisible)).Offset(, 6) = "x" Cells.AutoFilter Next 'Delete unmarked rows Rows(1).Insert Cells(1, 1) = "Sacrifice" Cells.AutoFilter Field:=7, Criteria1:="=" Intersect(ActiveSheet.UsedRange, _ Columns(1).SpecialCells(xlCellTypeVisible)).EntireRow.Delete 'Delete ATalanta rows Cells.AutoFilter Field:=1, Criteria1:="ATalanta*" Intersect(ActiveSheet.UsedRange, _ Columns(1).SpecialCells(xlCellTypeVisible)).EntireRow.Delete 'Clear filter column Columns(7).ClearContents Application.ScreenUpdating = True End Sub
07-17-2006, 12:24 PM
Djblois

wow you are the man. That works perfect
09-29-2006, 02:14 PM
rrtts

@MD - this is spot on and works GREAT!

One question though...how hard would it be (or is it even possible) to code into the formula something like the "Text to Columns" function...

My current process is I paste about 125 rows of text in column A, select Text to Columns and go through that whole process of setting up delimiters and then click the button with the macro you've created. It deletes the desired fields perfectly...but then the next time (3 times a day) I have to do this I have to set up "Text to Columns" again.

Anways...after much searching...I'm so glad to have found this tip...dozens of a tips on how to delete empty rows or specificed text from rows...this is the only one I found that allows you to set specific text to keep...

nice!
09-29-2006, 03:15 PM
mdmackillop

Can you post a sample containing your text and a note of the text to keep?
09-29-2006, 05:11 PM
rrtts

I have the macro set up to delete the exact text I need to, my problem is then automating the next step. I assume you mean a sample of the text I have left that I want to separate into columns by setting delimiters.
Basically it is 125 rows of data in the following format.

RHHIxxx/Unit Name
RHHMxxx/Unit Name
x=various values

Unit Name is always two parts - i.e. HMAS Waller

So, in a perfect world...

;)

I could cut and paste this into my worksheet...click my macro button that deletes the header and footer off of this text and have 3 columns of text.

RHHIABC/HMAS WALLER = RHHIABC HMAS WALLER (each in a column)

Hope that helps - and thanks for the help.
09-29-2006, 05:40 PM
mdmackillop

Use the Split function

Code:

Sub SplitText() Dim cel As Range For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) arr = Split(cel, "/") cel = arr(0) cel.Offset(, 1) = Split(arr(1), " ")(0) cel.Offset(, 2) = Split(arr(1), " ")(1) Next End Sub
10-03-2006, 12:29 PM
rrtts

I think I am doing something wrong...I keep getting a runtime error that says subscript out of range.

I've tinkered around with it, but obviously I'm not smart enough to figure it out. I cut and pasted your formula straight from the site, so I know I didn't type it in wrong.

Any ideas?

Thanks for the continued help!
10-03-2006, 12:48 PM
mdmackillop

Can you post some of your text in a workbook?
10-03-2006, 01:19 PM
rrtts

XXXXXXXX XXXXXXX0001 0010001-XXXX--XXXXXXX.
XXX XXXXX
X 010001Z XXX 06
FM ORIGINATOR
TO RHHIABC/UNIT 1
RHHIDEF/UNIT 2
RHHIABC/UNIT 3
RHHIABC/UNIT 4
RHHIABC/UNIT 5
RHHIDEF/UNIT 6
RHHIABC/UNIT 7
RHHIABC/UNIT 8
RHHIDEF/UNIT 9
RHHIABC/UNIT 10
RHHIABC/UNIT 11
RHHIDEF/UNIT 12
RHHIABC/UNIT 13
RHHIGHI/UNIT 14
RHHIABC/UNIT 15
RHHIABC/UNIT 16
RHHIGHI/UNIT 17
RHHIABC/UNIT 18
RHHIABC/UNIT 19
RHHIGHI/UNIT 20
RHHIABC/UNIT 21
RHHIABC/UNIT 22
RHHIGJO/UNIT 23
RHHIABC/UNIT 24
RHHIABC/UNIT 25
RHHIDEF/UNIT 26
RHHIABC/UNIT 27
SUBJ/TEST//
REMARKS/THIS IS A TEST MESSAGE. NO ACTION REQUIRED.//
END OF MESSAGE
NNNN

*Using your delete macro, I can delete the header/footer info and just have the "Unit" info in the routing indicator(RHHI***)/unit name format.

Now, I'm trying to use something similar to the "text to columns" function to separate the routing indicator (RHHI***) and unit name into two different columns.

That will lead to the final step of sorting each unit into a specific category based on routing indicator, but only the Unit name will appear...(all of it is just regular .txt data - I have the formula to do this thanks to xld)

Hope this helps (I don't have the worksheet on this machine or I would post it)
10-03-2006, 02:53 PM
mdmackillop

My previous code would fail if there was no / in the text. Try this version which skips any such values

Code:

Sub SplitText() Dim cel As Range For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) arr = Split(cel, "/") If InStr(1, cel, "/") = 0 Then GoTo Skipped cel = arr(0) cel.Offset(, 1) = Split(arr(1), " ")(0) cel.Offset(, 2) = Split(arr(1), " ")(1) Skipped: Next End Sub
10-03-2006, 03:47 PM
rrtts

I'm at a loss of words...you are too kind...my only other question (for now) is how did you get so smart at this...heh heh.

Everything works perfectly and you have made my job so much easier...I'm learning lots but also realize I have a LONG way to go.

Have a great day!
10-03-2006, 04:06 PM
mdmackillop

You just have to contribute answers to a few thousand questions and you pick up a bit of knowledge along the way! ...or you could buy a book.
10-03-2006, 05:52 PM
rrtts

Never mind...figured it out.
10-10-2006, 12:25 PM
rrtts

@MD - I ran into a bit of a snag I'm hoping you can help me with...

Can you explain your "Split Text" code - the problem I am having is that if there are multiple words then it deletes some of the text...

For example,

If the text is:

RHHIDEF/UNIT 2 (all in one cell), the result is RHHIDEF UNIT 2 (all in different cells - which is perfect).

But if the text is

RHHIDEF/UNIT 2 A B C (all in one cell), the result is RHHIDEF UNIT 2 (all in different cells - but A B C is deleted - which is bad).

Any ideas...thanks.

(Do you have any recommendations on Excel Books - I'm hesitant to just get the Excel 2003 for Dummies - but it might best fit my level of knowledge...heh heh).
10-10-2006, 03:10 PM
mdmackillop

Split creates an array of items as defined by the delimiter so
Arr = Split(RHHIDEF/UNIT 2 A B C,"/") will form a 2 item array
Arr(0) = RHHIDEF
Arr(1) = UNIT 2 A B C
The second bit of the code will split UNIT 2 A B C into 5 items using Space as a delimiter. If you want the A B C in a separate cell. you would need to concatenate these array items with spaces to reform the string.

You need to be sure of your data make up before you can write code to handle it.
10-10-2006, 04:03 PM
Ken Puls

Quote:

Originally Posted by mdmackillop

...or you could buy a book.

If only it were that easy. :rofl:
10-10-2006, 05:59 PM
rrtts

@MD...great!

Your original code was:

[vba]
Sub SplitText()
Dim cel As Range
For Each cel In Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
arr = Split(cel, "/")
If InStr(1, cel, "/") = 0 Then Goto Skipped
cel = arr(0)
cel.Offset(, 1) = Split(arr(1), " ")(0)
cel.Offset(, 2) = Split(arr(1), " ")(1)
Skipped:
Next
End Sub
[/vba]

After tinkering with it and your tips...I realized that once I deleted the 2nd cel.Offset I achieved the desired results.

It's amazing how simple this all seems once someone shows it to you...I feel dumb asking some of the questions I do only to realize a simple tweak gets the job done.

I do appreciate all the help...again.

@Ken Puls - Amen. I go to the local book store and try and read thru various books (like I know what I'm doing heh heh)...there are SO many books on the subject I need a book just to explain all the other books.