| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Keep Only Rows with The Word Saturday
Hello,
I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
Hi,
I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
OOPs,
I got it the wrong way around you want to keep Saturday Sub I_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Delete End If End Sub Mike "Mike H" wrote: Hi, I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
If InStr(UCase(c), "SATURDAY") = 0 And ....
You don't have to use the UCase function with InStr that way...InStr has an optional parameter to handle text casing. The above code fragment could be done this way also... If InStr(1, c, "Saturday", vbTextCompare) = 0 And ... -- Rick (MVP - Excel) "Mike H" wrote in message ... OOPs, I got it the wrong way around you want to keep Saturday Sub I_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Delete End If End Sub Mike "Mike H" wrote: Hi, I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)),
"SATURDAY") = 0 Then While I would probably use the InStr method myself, as an aside, the above test could also be done this way... If c & c.Offset(, 1) like "*[Ss][Aa][Tt][Uu][Rr][Dd][Aa][Yy]*" Then Although, if there is **any** chance that the end of Column D's text concatenated onto Column E's text could produce the word "Saturday" (in any casing arrangement) at the "join point", then place an intervening character (any character will do) between them to eliminate that possibility... If c & "-" & c.Offset(, 1) like "*[Ss][Aa][Tt][Uu][Rr][Dd][Aa][Yy]*" Then Though, for either of the two Like Operator constructions, it might be better to use the UCase function... If UCase(c & "." & c.Offset(, 1)) like "*SATURDAY*" Then -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If InStr(UCase(c), "SATURDAY") = 0 And .... You don't have to use the UCase function with InStr that way...InStr has an optional parameter to handle text casing. The above code fragment could be done this way also... If InStr(1, c, "Saturday", vbTextCompare) = 0 And ... -- Rick (MVP - Excel) "Mike H" wrote in message ... OOPs, I got it the wrong way around you want to keep Saturday Sub I_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Delete End If End Sub Mike "Mike H" wrote: Hi, I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
Thanks, Mike. I will try something similar as I'm not actually searching for
Saturday...I just wanted to keep the data kinda private. Sorry about that. I was looking at my data sets and realized that what I actually want to find is "pharmacy", "pharmacies", "drug" or "drugs" in text strings of any length in columns D and E but that won't count records twice if the macro 'sees' a pharmacy, pharmacies, drug, or drugs under columns D and E in the same record .. Would you be kind enough to step me through that process with a macro? Thanks again! "Mike H" wrote: OOPs, I got it the wrong way around you want to keep Saturday Sub I_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Delete End If End Sub Mike "Mike H" wrote: Hi, I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
Daren,
There are 2 approaches to this, you could hold all of the values to keep in an array or a worksheet range and this approach uses the latter with the range of value to keep being stored on Sheet 2 in Column A1 down. Once again right click the sheet tab with your data, view code and paste this in and run it. Also note that if you use INSTR in future be aware of Rick's guidance on it's use (Thanks Rick) Sub I_Like_Saturdays() Dim DelRange As Range, CompRange As Range Dim LastRowD As Long, LastrowE As Long, LastRowA As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row LastRowA = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set CompRange = Sheets("Sheet2").Range("A1:A" & LastRowA) Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If WorksheetFunction.CountIf(CompRange, c.Value) = 0 _ And WorksheetFunction.CountIf(CompRange, c.Offset(, 1).Value) = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.delete End If End Sub Mike "Daren" wrote: Thanks, Mike. I will try something similar as I'm not actually searching for Saturday...I just wanted to keep the data kinda private. Sorry about that. I was looking at my data sets and realized that what I actually want to find is "pharmacy", "pharmacies", "drug" or "drugs" in text strings of any length in columns D and E but that won't count records twice if the macro 'sees' a pharmacy, pharmacies, drug, or drugs under columns D and E in the same record . Would you be kind enough to step me through that process with a macro? Thanks again! "Mike H" wrote: OOPs, I got it the wrong way around you want to keep Saturday Sub I_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Delete End If End Sub Mike "Mike H" wrote: Hi, I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to Keep Only Rows with The Word Saturday
Ignore that last load of tosh your still using instr, still with a lest to
keep in shet 2, try this Dim LastRowD As Long, LastrowE As Long, LastRowA As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row LastRowA = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set CompRange = Sheets("Sheet2").Range("A1:A" & LastRowA) Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange DelFlag = True For Each d In CompRange If InStr(1, c, d, vbTextCompare) 0 Or InStr(1, c.Offset(, 1), d, vbTextCompare) 0 Then DelFlag = False Exit For End If Next If DelFlag Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.delete End If End Sub Mike "Mike H" wrote: Daren, There are 2 approaches to this, you could hold all of the values to keep in an array or a worksheet range and this approach uses the latter with the range of value to keep being stored on Sheet 2 in Column A1 down. Once again right click the sheet tab with your data, view code and paste this in and run it. Also note that if you use INSTR in future be aware of Rick's guidance on it's use (Thanks Rick) Sub I_Like_Saturdays() Dim DelRange As Range, CompRange As Range Dim LastRowD As Long, LastrowE As Long, LastRowA As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row LastRowA = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row Set CompRange = Sheets("Sheet2").Range("A1:A" & LastRowA) Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If WorksheetFunction.CountIf(CompRange, c.Value) = 0 _ And WorksheetFunction.CountIf(CompRange, c.Offset(, 1).Value) = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.delete End If End Sub Mike "Daren" wrote: Thanks, Mike. I will try something similar as I'm not actually searching for Saturday...I just wanted to keep the data kinda private. Sorry about that. I was looking at my data sets and realized that what I actually want to find is "pharmacy", "pharmacies", "drug" or "drugs" in text strings of any length in columns D and E but that won't count records twice if the macro 'sees' a pharmacy, pharmacies, drug, or drugs under columns D and E in the same record . Would you be kind enough to step me through that process with a macro? Thanks again! "Mike H" wrote: OOPs, I got it the wrong way around you want to keep Saturday Sub I_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") = 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") = 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Delete End If End Sub Mike "Mike H" wrote: Hi, I'm not sure if you want 'Saturday' in both or either of columns D & E. Right click your sheet tab, view code and paste this in and run it and it look for saturday in both columns Change this If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, to this If InStr(UCase(c), "SATURDAY") 0 OR InStr(UCase(c.Offset(, if it's either Sub I_Dont_Like_Saturdays() Dim DelRange As Range Dim LastRowD As Long, LastrowE As Long LastRowD = Cells(Cells.Rows.Count, "D").End(xlUp).Row LastrowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row Set MyRange = Range("D1:D" & WorksheetFunction.Max(LastRowD, LastrowE)) For Each c In MyRange If InStr(UCase(c), "SATURDAY") 0 And InStr(UCase(c.Offset(, 1)), "SATURDAY") 0 Then If DelRange Is Nothing Then Set DelRange = c.EntireRow Else Set DelRange = Union(DelRange, c.EntireRow) End If End If Next If Not DelRange Is Nothing Then DelRange.Select End If End Sub Mike "Daren" wrote: Hello, I have two columns that can contain the word Saturday in text values of any length. The word Saturday can appear in any cell in column D and E. Is there a macro that I can write to delete any row that do not contain the word Saturday in column D and E? Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to find a word and copy all rows with this word to diff ws | Excel Programming | |||
| Excel to run Word macro & Word returns value to be written in spreadsheet | Excel Programming | |||
| how to add word to the front of every word in all rows automatica. | Excel Discussion (Misc queries) | |||
| Cell colour chg when formula results equal a weekend day, i.e., "Saturday" and a date that falls on a Saturday? | Excel Programming | |||
| Macro to cut rows into another worksheet if a cell contains a specific word | Excel Programming | |||
Linear Mode
