Results 1 to 10 of 10

Loop with selection.delete and InStr is ending early

Threaded View

SCarroll Loop with selection.delete... 01-15-2022, 06:05 PM
TMS Re: Loop with... 01-15-2022, 08:32 PM
Bo_Ry Re: Loop with... 01-16-2022, 02:03 AM
SCarroll Re: Loop with... 01-16-2022, 01:23 PM
Artik Re: Loop with... 01-16-2022, 03:32 AM
SCarroll Re: Loop with... 01-16-2022, 01:57 PM
TMS Re: Loop with... 01-16-2022, 12:05 PM
TMS Re: Loop with... 01-16-2022, 01:41 PM
SCarroll Re: Loop with... 01-16-2022, 02:00 PM
TMS Re: Loop with... 01-16-2022, 02:07 PM
  1. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    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 ai&
    = [a1].CurrentRegion.Value
    For 1 To UBound(a)
        If 
    InStr(a(i1), ":"Then
            
    If InStr(a(i2), "photo") = 0 Then
                a
    (i4) = a(i3)
                
    a(i3) = a(i2)
            
    End If
            
    a(i2) = a(i1)
            
    a(i1) = Empty
        
    End If
    Next
    [a1].CurrentRegion.Value a
    End Sub 
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] instr function if loop to find data
    By diyVBA in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 05-06-2020, 11:20 AM
  2. [SOLVED] Macro Ending Early - When Using Range.Value on some macros?
    By bkm2016 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-28-2017, 04:41 PM
  3. Loop finishing too early!!
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-16-2013, 01:23 PM
  4. [SOLVED] How to break out of a For loop early?
    By geophysicist in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-12-2013, 11:08 PM
  5. For Exit Loop in instr function
    By drcheaud in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2011, 03:24 PM
  6. Ending macro early
    By TedH in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-20-2009, 11:45 AM
  7. Ending a macro early conditionally on one cell being blank
    By Rokuro kubi in forum Excel General
    Replies: 3
    Last Post: 05-26-2006, 09:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1