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.
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: