Printable View
I need a macro to scan a column of a spreadsheet with over 100,000 lines, validate whether a string "abcdefg" OR a string "hijklmn" in a cell. If there is no two strings in the cell, the line corresponding to the cell should be excluded.
Can you help?
Detail: the cells containing the text strings have beyond string. For example, a cell has value "abcdefgh123" and should not be deleted.
Hi
1. replace all blank cells with a special character (hit F5 > Special > Blanks > OK. Now type | and hit Ctrl + Enter )
2. hit ctrl + H find what: abcdefg leave empty the filed 'Replace with'
3. repeat # 2 for other string
4. hit F5 > Special > Blanks > OK. Alt > H > D > R (XL 2007 later) or Menu key (between Windows key and Ctrl key) > D > R
5. hit ctrl + H > find what: | leave empty the filed 'Replace with'
HI
empty lines I had already treated.
My problem now is scan the entire column (the report that the application generates and I copied to your clipboard windows groups all fields in a single column). In each cell, the macro or formula shall examine whether there are two distinct strings, and the text of each of the cell strings is not restricted to demand, since the report played all fields in a single column. If you do not find the strings, the routine deletes the line.
I wanted a macro to facilitate the process, but I know you can do this using nested functions. But there are more than 100,000 lines.
Hi.
the hint of F5 earned rest. Using F5, Find and ISERROR functions, could filter all records quickly.
Thank you!
In case you are still looking for a macro to do this, try
Code:Sub RemoveUnwantedRows()
Dim lng As Long
For lng = 100000 To 1 Step -1
If InStr(1, Cells(lng, "A"), "abcdefg") + InStr(1, Cells(lng, "A"), "hijklmn") = 0 Then
Rows(lng).Delete
End If
Next lng
End Sub
Hi Excel Fox
Thank you!!!