Macro to loop in all sheets except two

[Chandresh]

Hi Team,
I am using the below code to loop the macro in all sheets except 2 sheets however it runs on the first sheet and doesn't loop in others sheets, could you please help.

Sub Remove()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Macro" And ws.Name <> "ABC" Then

    'My Code
'Macro to remove data between two text
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
    strStart = "Client Name"
    strEnd = "NOTIONAL GAINS/ LOSS"
   
    DELETEMODE = False
    For r = 1 To Range("A" & Rows.Count).End(xlUp).Row  'first to last used row
       
        If Range("A" & r).Value = strEnd Then DELETEMODE = False
       
        If DELETEMODE Then
            'Create a Delete Range that will be used at the end
            If DelRng Is Nothing Then
                Set DelRng = Range("A" & r)
            Else
                Set DelRng = Application.Union(DelRng, Range("A" & r))
            End If
        End If
       
        If Range("A" & r).Value = strStart Then DELETEMODE = True
    Next r
   
    'Delete the Range compiled from above
    If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
  
  End If
Next ws

End Sub

 

[ouvay]

Hello
Try replacing the and with or
P.S not going through the rest of the code as I'm assuming its doing what you want it to

If ws.Name <> "Macro" Or ws.Name <> "ABC" Then

 

[JDSA84]

Can try this also I think it should loop until it sees the specific sheets you don't want.
I also suck at code so it might not work at all or delete everything somehow.

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

'My Code
'Macro to remove data between two text
Dim strStart As String, strEnd As String
Dim DELETEMODE As Boolean
Dim DelRng As Range
strStart = "Client Name"
strEnd = "NOTIONAL GAINS/ LOSS"

DELETEMODE = False
For r = 1 To Range("A" & Rows.Count).End(xlUp).Row 'first to last used row

If Range("A" & r).Value = strEnd Then DELETEMODE = False

If DELETEMODE Then
'Create a Delete Range that will be used at the end
If DelRng Is Nothing Then
Set DelRng = Range("A" & r)
Else
Set DelRng = Application.Union(DelRng, Range("A" & r))
End If
End If

If Range("A" & r).Value = strStart Then DELETEMODE = True
Next r
'Delete the Range compiled from above
If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
Goto NextIteration
End If
ws.Name(Sheet you dont want looped) or ws.Name(Sheet youd dont want looped2)
NextIteration:
Next

 

[dmt32]

Hi,
You have not qualified the Ranges to your ws object variable so code only applies to the activesheet

Untested but see if this update resolves your issue

Sub Remove()
    Dim DELETEMODE  As Boolean
    Dim DelRng      As Range
    Dim ws          As Worksheet
  
    Const strStart As String = "Client Name"
    Const strEnd As String = "NOTIONAL GAINS/ LOSS"
  
    For Each ws In ThisWorkbook.Worksheets
  
        If ws.Name <> "Macro" And ws.Name <> "ABC" Then
          
            'My Code
            'Macro to remove data between two text
          
            DELETEMODE = False
            For r = 1 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row        'first to last used row
              
                If ws.Range("A" & r).Value = strEnd Then DELETEMODE = False
              
                If DELETEMODE Then
                    'Create a Delete Range that will be used at the end
                    If DelRng Is Nothing Then
                        Set DelRng = ws.Range("A" & r)
                    Else
                        Set DelRng = Application.Union(DelRng, ws.Range("A" & r))
                    End If
                End If
              
                If ws.Range("A" & r).Value = strStart Then DELETEMODE = True
            Next r
          
            'Delete the Range compiled from above
            If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
          
        End If
        'clear object variable
        Set DelRng = Nothing
    Next ws
  
End Sub

Dave

 

[Chandresh]

Hello
Try replacing the and with or
P.S not going through the rest of the code as I'm assuming its doing what you want it to

If ws.Name <> "Macro" Or ws.Name <> "ABC" Then

Macro stops on below point

'Delete the Range compiled from above
If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp

 

[Chandresh]

Hi,
You have not qualified the Ranges to your ws object variable so code only applies to the activesheet

see if this update resolves your issue

Sub Remove()
    Dim DELETEMODE  As Boolean
    Dim DelRng      As Range
    Dim ws          As Worksheet
  
    Const strStart As String = "Client Name"
    Const strEnd As String = "NOTIONAL GAINS/ LOSS"
  
    For Each ws In ThisWorkbook.Worksheets
  
        If ws.Name <> "Macro" And ws.Name <> "ABC" Then
          
            'My Code
            'Macro to remove data between two text
          
            DELETEMODE = False
            For r = 1 To ws.Range("A" & ws.Rows.Count).End(xlUp).Row        'first to last used row
              
                If ws.Range("A" & r).Value = strEnd Then DELETEMODE = False
              
                If DELETEMODE Then
                    'Create a Delete Range that will be used at the end
                    If DelRng Is Nothing Then
                        Set DelRng = ws.Range("A" & r)
                    Else
                        Set DelRng = Application.Union(DelRng, ws.Range("A" & r))
                    End If
                End If
              
                If ws.Range("A" & r).Value = strStart Then DELETEMODE = True
            Next r
          
            'Delete the Range compiled from above
            If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
          
        End If
        'clear object variable
        Set DelRng = Nothing
    Next ws
  
End Sub

Dave

Thanks this is working fine

 

[Peter_SSs]

@Chandresh
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

@ouvay (and @dmt32 )
This line would be TRUE for every worksheet, including Macro and ABC

Try replacing the and with or

If ws.Name <> "Macro" Or ws.Name <> "ABC" Then

Thanks this is working fine

Perhaps it does not matter to you, but refer my point above. The code will not be skipping Macro or ABC sheets.
Edit: The previous sentence is no longer relevant as the issue that I was referring to has been edited in post #4

This is my modification that does skip those two sheets

Sub Remove_v2()
  Dim ws As Worksheet
  Dim strStart As String, strEnd As String
  Dim DELETEMODE As Boolean
  Dim DelRng As Range
  Dim r As Long
 
  strStart = "Client Name"
  strEnd = "NOTIONAL GAINS/ LOSS"
 
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Macro" And ws.Name <> "ABC" Then
      DELETEMODE = False
      Set DelRng = Nothing
      With ws
       For r = 1 To .Range("A" & Rows.Count).End(xlUp).Row  'first to last used row
           If .Range("A" & r).Value = strEnd Then DELETEMODE = False
         
           If DELETEMODE Then
               'Create a Delete Range that will be used at the end
               If DelRng Is Nothing Then
                   Set DelRng = .Range("A" & r)
               Else
                   Set DelRng = Application.Union(DelRng, .Range("A" & r))
               End If
           End If
         
           If .Range("A" & r).Value = strStart Then DELETEMODE = True
       Next r
       'Delete the Range compiled from above
       If Not DelRng Is Nothing Then DelRng.EntireRow.Delete xlShiftUp
      End With
    End If
  Next ws
End Sub

 

[dmt32]

This line would be TRUE for every worksheet, including Macro and ABC

@Peter_SSs not sure why you mentioned me in your post

I posted OPs original code construct.

If ws.Name <> "Macro" And ws.Name <> "ABC" Then

Dave

 

[Peter_SSs]

@Peter_SSs not sure why you mentioned me in your post

..because I must have looked at your post before it was edited