How to remove row which contains space with word zk and space?

Question

How can If InStr be used to remove a row which contain space with word zk and space, like " zk "?

Sub RemoveRows1()    
Dim j As Long

j = 1

Do While j <= ThisWorkbook.ActiveSheet.Range("D1").CurrentRegion.Rows.Count

    If InStr(1, ThisWorkbook.ActiveSheet.Cells(j, 4).Text, "(L/C)", vbTextCompare) > 0 Then
        ThisWorkbook.ActiveSheet.Cells(j, 4).EntireRow.Delete

    ElseIf InStr(1, ThisWorkbook.ActiveSheet.Cells(j, 4).Text, "ABC", vbTextCompare) > 0 Then
        ThisWorkbook.ActiveSheet.Cells(j, 4).EntireRow.Delete

    ElseIf InStr(1, ActiveSheet.Cells(j, 4).Text, " ZK ", vbTextCompare) > 0 Then Rows(j).EntireRow.Delete

    Else
        j = j + 1
    End If

Loop
End Sub

Try this: If InStr(1, ActiveSheet.Cells(j, 4).Text, " zk ", vbTextCompare) > 0 Then Rows(j).EntireRow.Delete It seems, that your reference value did not work properly as well as the deletion method. — prextor, Jul 07 '20 at 11:14
yes we have row with string: abcabc abc zk abc How do we romove row with space zk space ? — peter cook, Jul 07 '20 at 11:18
why does program remove for example "ZK" and does not romove " ZK " with spaces? how do i remake the program to remove spaces with word? — peter cook, Jul 09 '20 at 08:44

Havard Kleven · Answer 1 · 2020-07-07T12:37:37.373

0

@prextor's answer in the comments works perfectly. I edited your code so that the argument in your If-statement that stores the final row in column D is stored in a separate variable, rather than a part of the statement.

Sub RemoveRows1()
    Dim j As Long
    Dim fRow as Long
    j = 1
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    With ws
        fRow = .Cells(.Rows.Count, 4).End(xlUp).Row
    End With

    Do While j <= fRow
        If InStr(1, ActiveSheet.Cells(j, 4).Text, " zk ", vbTextCompare) > 0 Then
            Rows(j).EntireRow.Delete
        Else
            j = j + 1
        End If
    Loop

End Sub

edited Jul 07 '20 at 12:37

answered Jul 07 '20 at 11:51

Havard Kleven

422
6
19

I think you forgot `Dim fRow As Long` :) Just in case of `Option Explicit` – Super Symmetry Jul 07 '20 at 12:36
Agreed, should've included that! Edited. :-) – Havard Kleven Jul 07 '20 at 12:37
last example remove spaceБУ and does not remove space БУ space. how do we remove row with " БУ " ? – peter cook Jul 09 '20 at 09:35

Dominique · Answer 2 · 2020-07-07T12:56:20.680

0

Did you try removing in the opposite direction? Let me give you an example:

for i=1 to 5, if line[i] contains " zk ", then remove that line.

As an input we give:

1. rubbish
2.  zk 
3.  zk 
4. rubbish again
5.  zk

The following will happen:

for i=1, nothing is done.
for i=2, line 2 will be deleted, so your file becomes:

1. rubbish
3.  zk 
4. rubbish again
5.  zk

for i=3:

line[i] = "4. rubbish again"

Now you scream "No, for i=3 the line is the one which starts with "3. ", but then you realise that, because of the previous deletion, your lines get shifted and the line for i=3 is indeed the one, starting with "4." (your index is jumping of the line you want to delete).

So, how to solve this? Very easy: instead of doing:

for i=1 to 5 ... next

You do:

for i=5 to 1 ... step -1

And there, the deletion and the corresponding shifting of the lines do not cause your index to jump over the lines you want to delete.

On top of this, you are referring to ActiveSheet, CurrentRegion all through your while-loop, it might be safer to store those into local variables in order for those things not to be messed up during the loop.

edited Jul 07 '20 at 12:56

answered Jul 07 '20 at 12:13

Dominique

16,450
15
56
112

I agree with you: The most natural and elegant way to delete inside a loop is to count in reverse. However, I don't see anything wrong with counting forward if the loop counter is edited appropriately. The code provided in the question increments `j` if and only if no deletion occurs and, therefore, should wok properly when counting forward. Note: the code uses `Do While Loop` not `For` loop. Even in a `For` loop you can easily decrement the counter `i = i - 1` if deletion happens: Not very elegant, but works. – Super Symmetry Jul 07 '20 at 12:48
@SuperSymmetry: you're right, but there are other issues in the code. I've addressed them in the edition of my answer. – Dominique Jul 07 '20 at 12:57
I agree with you in those issues, too. And by the way, I really like the way you explained the forward/backward counting issues. – Super Symmetry Jul 07 '20 at 12:59

score 0 · Answer 3 · answered Jul 07 '20 at 12:23

I do not see any issue with the flow of your algorithm and it worked well on my computer. However, a possible source of error is using .Text rather than .Value Please see this post. Try changing the If Instr(... line to

If InStr(1, ThisWorkbook.ActiveSheet.Cells(j, 4).Value, " zk ", vbTextCompare) > 0 Then

and see if it helps.

On a separate note, I find the best way to delete a range is to build the range to delete inside the loop and then perform the deletion outside the loop in one step. This is much more efficient and a lot easier to test as you can select the range in the testing stage and once you are happy that your code is identifying the correct range, you can then change your code to delete the range.

See this code for example:

Sub RemoveRows1()

    Dim j As Long
    Dim lRows As Long
    Dim rngToDelete As Range
    
    With ThisWorkbook.ActiveSheet
        
        j = 1
        lRows = .Range("D1").CurrentRegion.Rows.Count
        '.Range("D1").CurrentRegion.Select
        Do While j <= lRows
        
            If InStr(1, .Cells(j, 4).Value, " zk ", vbTextCompare) > 0 Then
                
                ' Build the range to delete here
                ' If rngToDelete is nothing, you will get an error
                ' if you use Union()
                If rngToDelete Is Nothing Then
                    Set rngToDelete = .Cells(j, 4)
                Else
                    Set rngToDelete = Union(.Cells(j, 4), rngToDelete)
                End If
               
            End If
            
            j = j + 1
        
        Loop
        
        ' If there's something to delete, delete all at the same time
        ' This way is way more efficient than deleting each row in a loop
        If Not rngToDelete Is Nothing Then
        
          ' Remove the .Select line and uncomment the next line
          ' once you have tested the right rows have been selected
          rngToDelete.EntireRow.Select
          'rngToDelete.EntireRow.Delete
        
        End If
        
    End With
    
End Sub

why does program remove for example "БУ" and does not romove " БУ " with spaces? how do i remake the program to remove spaces with word? — peter cook, Jul 09 '20 at 08:59
Well, it worked well for me! Please be more specific, what exactly doesn't work? Does it give you an error message? Do you understand the flow of the code? The code as it stands should only remove/select all rows within the specified range that contain the string " zk " (or any upper/lower combiations) in the fourth column. — Super Symmetry, Jul 09 '20 at 10:55

How to remove row which contains space with word zk and space?

3 Answers3