Delete rows with certain number in text string in a cell

Question

I have a list with about 500 data entries and some of them have different text with the same number in a cell. I need to find all those rows with "00000" in it and delete them. I need to calculate the time when "test2" startet no working(18:23) and need to know how long it took that it worked again(18:30) and the error00000 message is messing my formulas up so i need to delete all rows with error00000 in it. the error messege is never the same but "000000" in it is always the same.

I don't know how to find it with VBA code because I only can find numbers alone in cells. The data entries can change per week so it should be with VBA. I have some code but its online working when the number is alone in a cell and not in a sentence like Error message 00000 the text is always changing, the number stays the same.

My code for deleting rows with numbers alone in a cell

Private Sub CommandButton1_Click()
    Dim lRow As Long
    Dim iCntr As Long
    lRow = 1000
    For iCntr = lRow To 1 Step -1
        If Cells(iCntr, 5).Value = "1008" Then
            Rows(iCntr).Delete
        End If
    Next
End Sub



03.09.2015 18:30    test2   EXAMPLETEXT blablablalb error000002
03.09.2015 18:25    test2   EXAMPLETEXT blablablalb error000000
03.09.2015 18:23    test2   EXAMPLETEXT blablablalb error000002
03.09.2015 18:30    test1   EXAMPLETEXT blablablalb error000002
03.09.2015 18:25    test1   EXAMPLETEXT blablablalb error000000
03.09.2015 18:23    test1   EXAMPLETEXT blablablalb error000002

I already answered a similar question few days ago. Please search stackoverflow — Siddharth Rout, Sep 11 '15 at 12:20
And [Here](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) is link to a very old question. Here I am copying the result to another sheet. You can simply delete that range. — Siddharth Rout, Sep 11 '15 at 12:22
Siddharth Routh in your answers but i dind't find any question which has anything to do with mine... And what has "How to copy a line in excel using a specific word and pasting to another excel sheet?" to do with "Delete rows with certain number in text string in a cell" ? — Swi, Sep 11 '15 at 12:27
oh sorry, my english is not the best. i missed that.. I still don't get the code. i don't understand it What do i have to use? i need to find 0000000 in a cell with text The text can change so i tried "*000000*" which clearly isn't working... could this even work with my kind of code? — Swi, Sep 11 '15 at 12:29
Can you upload a screenshot of your data. Add it to any file uploading site and share the link here — Siddharth Rout, Sep 11 '15 at 12:45
I postet an example i can't uploud any of my table because of data protection. i have more collums but that shoudn't matter. the "macro" should delete all rows with 000000 in the text in the third row ): — Swi, Sep 11 '15 at 13:04
I to bad in VBA coding that i know which code i have to use ehen i don't want the copy stuff.... can you tell me that please? — Swi, Sep 11 '15 at 13:18
Please update the question with the autofilter code that you are trying — Siddharth Rout, Sep 11 '15 at 13:19
oh ok, but thank you very much for not giving up that fast with me! I would give you a like or something but i can't thank you really much! — Swi, Sep 11 '15 at 13:35
I wouldn't have given up (I rarely give up) if you would "Listen" to me... Guess I got tired "repeating" myself :) — Siddharth Rout, Sep 11 '15 at 13:37
I didin't know what you have ment up there sorry, my english not my motherlanguage uts hard to work with words like codingstuff an so on — Swi, Sep 11 '15 at 13:46
That is ok. I will visit this thread after couple of hours. If it is still not sorted then I will jump in... — Siddharth Rout, Sep 11 '15 at 13:53
hey @SiddharthRout can you help me? the code from isn'T working ): need help please — Swi, Sep 14 '15 at 05:32
ok. can you do me a favor. Can you update your exact request in the question above. I don't want to read all those comments again. In your question ENSURE that you explain what do you want in details and then I will surely help you. — Siddharth Rout, Sep 14 '15 at 06:43

Zeeshan S. · Answer 1 · 2015-09-11T13:37:29.753

0

Try running the below code to delete filtered rows. Before you run the code, make sure you enter values for the variables strSearch, strSearchColName and strSheetName.

Sub Delete_Rows_Condtionally()
    Dim lastRow As Long
    Dim currentRow As Long
    Dim cellText As String
    Dim strSearch As String
    Dim strSearchColName As String

    strSearch = "000000"
    strSearchColName = "E"
    strSheetName = "tabelle2"

    With ThisWorkbook.Worksheets(strSheetName)

        'Get last row
        lastRow = .Range(strSearchColName & .Rows.Count).End(xlUp).Row
        currentRow = lastRow
        Do While currentRow >= 1
            cellText = .Range(strSearchColName & currentRow).Text
            If (InStr(1, cellText, strSearch)) Then
                .Range(strSearchColName & currentRow).EntireRow.Delete
            Else
                currentRow = currentRow - 1
            End If
        Loop

    End With

End Sub

edited Sep 11 '15 at 13:37

answered Sep 11 '15 at 12:59

Zeeshan S.

2,041
2
21
40

What makes you think this is a duplicate? If some part of it was already answered elsewhere, it doesn't mean the entire question is duplicate. – Zeeshan S. Sep 11 '15 at 13:01
"Some Part"? The linked question has all the code the OP needs and has to just change one line in that – Siddharth Rout Sep 11 '15 at 13:03
I'm sure you could have answered it then. – Zeeshan S. Sep 11 '15 at 13:05
Yes I could have but didn't as it is not ethical. And hence I gave a link to OP. If you are planning to delete the question then I will reverse the downvote :) – Siddharth Rout Sep 11 '15 at 13:07
That doesn't really help me, i need to know how i can delete the rows with "000000" in a text. I can't filter it to delete rows because there are formulas which are calculating stuff in the table... – Swi Sep 11 '15 at 13:08
@Swi - How does filtering affect the formula calculations? They should work regardless if you filter or not. If you follow Siddharth's example, you can filter for `*000000*` and then delete the visible cells in your range. Then remove the filter. At worse, you have to modify is exact code a bit to fit your specific needs. But what you have all you need there - and more :) – Scott Holtzman Sep 11 '15 at 13:10
I would be really happy if you answer my question because i don't know how to work with the link you gave me – Swi Sep 11 '15 at 13:11
@ScottHoltzman I have over 500 data entries with a lot of collums my code up there is deleteing all rows with specifig numbers(not only one like shown up there) so that only rows with 3 specifing numbers stay. then I#m sorting the rows acording to a type and then i'm calculating the time how long the dates are apart. The rows with "000000" in the text are messing everything up. so i have to get them away. – Swi Sep 11 '15 at 13:16
@swi - have you even tried to work with any of the code posted - this answer or Siddharth's link? Have you tried to study the concepts and apply them to your problem? – Scott Holtzman Sep 11 '15 at 13:20
@Scott yes. but i don't know which part i need beacause i don't need the copy stuff. – Swi Sep 11 '15 at 13:25
@SiddharthRout The code has been updated as per new requirements which do not want the data to be filtered. It is no longer a duplicate question nor a duplicate answer. Please undo the downvote as that would be the ethical thing to do. – Zeeshan S. Sep 11 '15 at 13:29
With ThisWorkbook.Worksheets(strSheetName) what do ihave to put in there? With ThisWorkbook.Worksheets(strtabelle2) my sheet is called tabelle2? – Swi Sep 11 '15 at 13:34
@Swi Modify only the assignment of the variables: strSearch = "000000" strSearchColName = "E" strSheetName = "Sheet1" Leave the rest as is and run. In this case, strSheetName = "tabelle2" – Zeeshan S. Sep 11 '15 at 13:35
it stops working there.... i put it in a commandbutton, could this be the problem? – Swi Sep 11 '15 at 13:37
Leave 'With ThisWorkbook.Worksheets(strSheetName)' as is. Do not modify that. Modify only the variable assignments 'strSearch', 'strSearchColName' and 'strSheetName'. – Zeeshan S. Sep 11 '15 at 13:38
What is the column name in which all the 'error000000' strings are entered? – Zeeshan S. Sep 11 '15 at 13:43
strSearch = "00000000" strSearchColName = "J" strSheetName = "tabelle2" it stops working there With ThisWorkbook.Worksheets(strSheetName) – Swi Sep 11 '15 at 13:44
Update the question details section with a screenshot of your data set. The first 10 rows and the column in which the string is to be searched should be visible in the screenshot. – Zeeshan S. Sep 11 '15 at 13:48
i can't, i already said why. it tells me runtime error, always – Swi Sep 11 '15 at 13:53
Are you running the above code as a sub-part? Is any other VBA interfering with this code? The code I provided is working flawlessly on the sample data provided by you. Due to lack of accurate information I'm not sure how to further debug your problem. – Zeeshan S. Sep 11 '15 at 13:57
no there only is this code, It stops there "With ThisWorkbook.Worksheets(strSheetName)" when i say debug – Swi Sep 11 '15 at 13:59

Delete rows with certain number in text string in a cell

1 Answers1