PDA

View Full Version : Delete all rows that don't start with CRN or AT



Djblois
07-17-2006, 09:14 AM
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.

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

Djblois
07-17-2006, 09:36 AM
Always different

mdmackillop
07-17-2006, 09:37 AM
10, 100, 60000?

Djblois
07-17-2006, 09:41 AM
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.

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

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

mdmackillop
07-17-2006, 10:32 AM
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


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

Djblois
07-17-2006, 11:10 AM
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)

mdmackillop
07-17-2006, 11:29 AM
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

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

Djblois
07-17-2006, 12:24 PM
wow you are the man. That works perfect

rrtts
09-29-2006, 02:14 PM
@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!

mdmackillop
09-29-2006, 03:15 PM
Can you post a sample containing your text and a note of the text to keep?

rrtts
09-29-2006, 05:11 PM
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.

mdmackillop
09-29-2006, 05:40 PM
Use the Split function

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

rrtts
10-03-2006, 12:29 PM
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!

mdmackillop
10-03-2006, 12:48 PM
Can you post some of your text in a workbook?

rrtts
10-03-2006, 01:19 PM
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)

mdmackillop
10-03-2006, 02:53 PM
My previous code would fail if there was no / in the text. Try this version which skips any such values

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

rrtts
10-03-2006, 03:47 PM
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!

mdmackillop
10-03-2006, 04:06 PM
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.

rrtts
10-03-2006, 05:52 PM
Never mind...figured it out.

rrtts
10-10-2006, 12:25 PM
@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).

mdmackillop
10-10-2006, 03:10 PM
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.

Ken Puls
10-10-2006, 04:03 PM
...or you could buy a book.

If only it were that easy. :rofl:

rrtts
10-10-2006, 05:59 PM
@MD...great!

Your original code was:


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


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.