Loop with selection.delete and InStr is ending early

**SCarroll** · 01-15-2022, 06:05 PM

Hello: I can not for the life of me figure this out. I have even switched the loop to go from Last row to 2 (starting from bottom up) and no matter what I do my loop ends before it is done. I'm not sure if it has to do with the delete nature of the command?

Background: I'm cleaning up a large set of data. I've provided a small sample to illustrate my problem.

The first loop appears to work flawlessly. The first loop scrolls through and shifts any cell containing ":" to the right. Next I want to scroll through Column C and remove and shift cells to the left for cells with text containing "photo". This second loop never makes it to the end. I have tried re-defining the last row, ranges, etc. Would anyone have any brilliant ideas?

I've attached the sample spreadsheet for you with the VBA code.

Original sample data is on one tab and you can see where the code ends in Column C on the Cleaned Up data tab.

Hopefully this makes sense.

Thanks,
Stef,

**TMS** · 01-15-2022, 08:32 PM

This works:

Sub CleanUp_TMS()

Dim lastrow As Long
Dim cel As Range

'this will move region to column b and leave a 2nd location if there is one in column a
lastrow = Range("A" & rows.Count).End(xlUp).Row

Debug.Print Range("a2:a" & lastrow).Address
For Each cel In Range("a2:a" & lastrow)
    Debug.Print cel.Address, cel.Value
    If InStr(cel.Value, ":") > 0 Then
        cel.Insert Shift:=xlToRight
    End If
Next cel
 
'remove photo lines out of Column C
Debug.Print Range("c2:c" & lastrow).Address
For Each cel In Range("C2:C" & lastrow)
    Debug.Print cel.Address, cel.Value
    If InStr(cel.Value, "photo") > 0 Then
        cel.Delete Shift:=xlToLeft
    End If
Next cel

End Sub

EXCEPT ... I am at a loss to explain why cell C3 is skipped in the second loop.

$A$2:$A$18
$A$2 Hamilton : Hamilton-Wentworth
$A$3 Hamilton : Hamilton-Wentworth
$A$4 Dundas : Hamilton-Wentworth
$A$5 Stoney Creek : Hamilton-Wentworth
$A$6 Hamilton : Hamilton-Wentworth
$A$7 425 WILSON ST E
$A$8 Flamborough : Hamilton-Wentworth
$A$9 Dundas : Hamilton-Wentworth
$A$10 Flamborough : Hamilton-Wentworth
$A$11 135 HWY 8
$A$12 UNITS C11 & C1A
$A$13 Hamilton : Hamilton-Wentworth
$A$14 Dundas : Hamilton-Wentworth
$A$15 Hamilton : Hamilton-Wentworth
$A$16 Hamilton : Hamilton-Wentworth
$A$17 Hamilton : Hamilton-Wentworth
$A$18 Hamilton : Hamilton-Wentworth
$C$2:$C$18
$C$2 street photos:*1*2*3*4*5*6*7*8*9*10*11*12*13*14*15*16*17*18*19*20*21*22*23*24*25*26*27
$C$4 street photos:*1*2*3*4*5*6*7
$C$5 street photos:*1*2*3*4*5*6*7*8*9*10*11*12*13*14*15*16*17*18
$C$6 street photos:*1*2*3*4*5*6*7*8*9*10*11*12*13*14*15*16*17
$C$7
$C$8 street photos:*1*2*3*4*5*6*7
$C$9 street photos:*1*2*3*4*5*6*7
$C$10 street photos:*1*2*3*4*5*6
$C$11
$C$12
$C$13 Transferor(s)
$C$14 street photos:*1*2*3*4*5
$C$15 street photos:*1*2*3*4*5
$C$16 street photos:*1*2*3*4*5
$C$17 street photos:*1*2*3*4*5
$C$18 Transferor(s)

**Bo_Ry** · 01-16-2022, 02:03 AM

Seem like deleting the first cell C2 on range "C2:C18" cause the C3 to skip
Change C2 to C1 fix it

Sub CleanUp_TMS()

Dim lastrow As Long
Dim cel As Range

'this will move region to column b and leave a 2nd location if there is one in column a
lastrow = Range("A" & rows.Count).End(xlUp).Row

Debug.Print Range("a2:a" & lastrow).Address
For Each cel In Range("a2:a" & lastrow)
    Debug.Print cel.Address, cel.Value
    If InStr(cel.Value, ":") > 0 Then
        cel.Insert Shift:=xlToRight
    End If
Next cel
 
'remove photo lines out of Column C
Debug.Print Range("c2:c" & lastrow).Address
For Each cel In Range("C1:C" & lastrow)
    Debug.Print cel.Address, cel.Value
    If InStr(cel.Value, "photo") > 0 Then
        cel.Delete Shift:=xlToLeft
    End If
Next cel

End Sub

or try

PHP Code:


Sub cUp()



Dim a, i&

a = [a1].CurrentRegion.Value

For i = 1 To UBound(a)

    If InStr(a(i, 1), ":") Then

        If InStr(a(i, 2), "photo") = 0 Then

            a(i, 4) = a(i, 3)

            a(i, 3) = a(i, 2)

        End If

        a(i, 2) = a(i, 1)

        a(i, 1) = Empty

    End If

Next

[a1].CurrentRegion.Value = a

End Sub

**SCarroll** · 01-16-2022, 01:23 PM

Thank you so very much!!! Both of your solutions worked. I understand the first one better though even though it is longer or more typing! hehe But honestly... so appreciated. I need to learn to ask here sooner than bang my head and struggle for hours.

**Artik** · 01-16-2022, 03:32 AM

From my tests, it appears that a reference to the range C2: C18 (in the test - the variable Rng) behaves differently. When the first or last cell of a range is deleted, this reference changes its address. However, if cells from the second to the penultimate one are deleted - the reference remains unchanged. I cannot explain why removing cells at the borders changes the address, and removing cells inside does not.
Macro for testing only:

Sub CleanUp_Test_Art()

    Dim lastrow     As Long
    Dim cel         As Range
    Dim Rng         As Range

    'this will move region to column b and leave a 2nd location if there is one in column a
    lastrow = Range("A" & Rows.Count).End(xlUp).Row

    Set Rng = Range("A2:A" & lastrow)
    Debug.Print "Rng.Address: " & Rng.Address
    
    For Each cel In Rng
        Debug.Print cel.Address(0, 0) & " | " & IIf(InStr(cel.Value, ":") > 0, "To insert", "It remains")
        
        If InStr(cel.Value, ":") > 0 Then
            cel.Insert Shift:=xlToRight
        End If
        
        Debug.Print IIf(InStr(cel.Value, ":") > 0, "After insert: ", "After: ") & cel.Address(0, 0)
        Debug.Print "Rng.Address: " & Rng.Address(0, 0)
    Next cel

    Debug.Print String(10, "- ")

    'remove photo lines out of Column C
    Set Rng = Range("C2:C" & lastrow)
    Debug.Print "Rng.Address: " & Rng.Address
    
    On Error Resume Next
    
    For Each cel In Rng
        Err.Clear
        Debug.Print cel.Address(0, 0) & " | " & IIf(InStr(cel.Value, ":") > 0, "To delete", "It remains")
        If InStr(cel.Value, "photo") > 0 Then
            cel.Delete Shift:=xlToLeft
        End If
        Debug.Print "After: " & cel.Address(0, 0)
        If Err.Number <> 0 Then
            Debug.Print Err.Description
        End If
        Debug.Print "Rng.Address: " & Rng.Address(0, 0)
    Next cel

End Sub

Another solution to the problem is to use a descending loop:

Sub CleanUp_Art2()

    Dim lastrow     As Long
    Dim cel         As Range
    Dim Rng         As Range
    Dim i           As Long

    'this will move region to column b and leave a 2nd location if there is one in column a
    lastrow = Range("A" & Rows.Count).End(xlUp).Row

    Set Rng = Range("A2:A" & lastrow)
    
    For Each cel In Rng
        If InStr(cel.Value, ":") > 0 Then
            cel.Insert Shift:=xlToRight
        End If
    Next cel
    
    'remove photo lines out of Column C
    For i = lastrow To 2 Step -1
        If InStr(Cells(i, "C").Value, "photo") > 0 Then
            Cells(i, "C").Delete Shift:=xlToLeft
        End If
    Next i

End Sub

Artik

**SCarroll** · 01-16-2022, 01:57 PM

Thank you also for taking time to look at this. I had also tried the descending loop but it didn't work for me either. It was a headscratcher for sure! Thanks so much for the help!

**TMS** · 01-16-2022, 12:05 PM

@Bo_Ry/Artik: thanks for the workarounds. I am still intrigued that the "fail" is inconsistent. If the loop had missed other cells, I could understand it to a certain extent and then the traditional workaround of working from the bottom up would make more sense. But just one cell near the top of the loop is very confusing.

Anyway, my workaround

Option Explicit

Sub CleanUp_TMS_v2()

Dim lastrow As Long
Dim cel As Range, rDel As Range

'this will move region to column b and leave a 2nd location if there is one in column a
lastrow = Range("A" & rows.Count).End(xlUp).Row

Debug.Print Range("a2:a" & lastrow).Address
For Each cel In Range("a2:a" & lastrow)
    Debug.Print cel.Address, cel.Value
    If InStr(cel.Value, ":") > 0 Then
        cel.Insert Shift:=xlToRight
    End If
Next cel
 
'remove photo lines out of Column C
Debug.Print Range("c2:c" & lastrow).Address
For Each cel In Range("C2:C" & lastrow)
    Debug.Print cel.Address, cel.Value
    If InStr(cel.Value, "photo") > 0 Then
        If rDel Is Nothing Then
            Set rDel = cel
        Else
            Set rDel = Union(rDel, cel)
        End If
    End If
Next cel

If Not rDel Is Nothing Then rDel.Delete Shift:=xlToLeft

End Sub

**TMS** · 01-16-2022, 01:41 PM

Not sure which solution you opted for. My first solution was close but, for some reason, missed a cell out in the second loop. That might have been masked in your live file. My second solution works ok.

I'm guessing the solutions offered by Bo_Ry and Artik worked but I didn't test them.

You don't need to type any solutions; you can copy and paste them.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**SCarroll** · 01-16-2022, 02:00 PM

Thanks for the tip on the star!!

I didn't notice that before. You all rock in here!

**TMS** · 01-16-2022, 02:07 PM

You're welcome. Thanks for the rep.

Loop with selection.delete and InStr is ending early

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Re: Loop with selection.delete and InStr is ending early

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] instr function if loop to find data

[SOLVED] Macro Ending Early - When Using Range.Value on some macros?

Loop finishing too early!!

[SOLVED] How to break out of a For loop early?

For Exit Loop in instr function

Ending macro early

Ending a macro early conditionally on one cell being blank

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions