Macro to loop in all sheets except two

Chandresh

Board Regular
Joined
Jul 21, 2009
Messages
146
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.

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

VBA Code:
If ws.Name <> "Macro" Or ws.Name <> "ABC" Then
 
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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

Rich (BB code):
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
 
Last edited:
Upvote 0
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

VBA Code:
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
 

Attachments

  • Capture.JPG
    Capture.JPG
    24.8 KB · Views: 8
Upvote 0
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

Rich (BB code):
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
 
Upvote 0
@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

VBA Code:
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

VBA Code:
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
 
Last edited:
Upvote 0
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.

VBA Code:
If ws.Name <> "Macro" And ws.Name <> "ABC" Then

Dave
 
Upvote 0

Forum statistics

Threads
1,226,223
Messages
6,189,710
Members
453,566
Latest member
ariestattle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top