Re: Loop with selection.delete and InStr is ending early
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
Bookmarks