Run script on all sheets excel

Question

I have this script that searches for a defined string and deletes the column if it finds it. I want to run this same search across all sheets in the workbook. So far I have tried setting it up like this. But it will only run on the Active Sheet.

Sub RunMacroOnAllSheetsToRight()
For i = ActiveSheet.Index To Sheets.Count
    Call MyFunction(i)
Next i
End Sub


Function MyFunction(i)
Dim c As Range
            Dim str As String

            str = "SearchStringHere"

            For Each c In ActiveSheet.UsedRange
               If InStr(c.Value, str) > 0 Then
               c.EntireColumn.Delete Shift:=xlToLeft
               End If
            Next c

End Function

Script now cycles through but only deletes single columns for some reason. Needs to be able to match and delete multiple columns per sheet.

Use a `For each ws in thisbook.worksheets` loop [similar question](http://stackoverflow.com/a/20422491/4002530) — tospig, Mar 20 '15 at 07:02
possible duplicate of [Excel Macro : loop through Excel Sheets](http://stackoverflow.com/questions/20422356/excel-macro-loop-through-excel-sheets) — Maciej Los, Mar 20 '15 at 07:11

R3uK · Accepted Answer · 2015-03-22T22:24:38.480

1

You just need to change ActiveSheet in your "function" and it'll be better!

BTW : You can have a Sub with paramaters (as below) and a function is only necessary when you have an output result (that you don't have in your example).

2 main changes : - added Set Ws=Nothing to free Ws - changed incremental going through columns, because when delete a column going increasingly, you miss to analyse next column(j+1)which is now the column(j)

Sub RunMacroOnAllSheetsToRight()
Application.ScreenUpdating = False
For i = ActiveSheet.Index To Sheets.Count
    Column_Delete i, "SearchStringHere"
Next i
Application.ScreenUpdating = True
End Sub


Sub Column_Delete(ByVal Sheets_Index As Integer, ByVal Str_to_Find As String)

Dim Ws As Worksheet
Set Ws = Worksheets(Sheets_Index)


Dim EndColumn As Integer
EndColumn = Ws.Cells(1, Columns.Count).End(xlToLeft).Column

'descending travel of the columns as we are going to delete some of them
For j = 1 To EndColumn
    If InStr(Ws.Cells(1, EndColumn - j + 1), Str_to_Find) > 0 Then
        Ws.Columns(EndColumn - j + 1).EntireColumn.Delete Shift:=xlToLeft
    End If
Next j

'Don't forget to free Ws (like I did...)
Set Ws = Nothing

End Sub

edited Mar 22 '15 at 22:24

answered Mar 20 '15 at 08:32

R3uK

14,417
7
43
77

This works great but will only remove 1 column from each sheet. If the identified column appears more than once the script has to be re-run. Can this be corrected? – RichT Mar 20 '15 at 16:29
Adding a boolean to set and reset the possibility of action might work, do you need help for that kind of thing? – R3uK Mar 20 '15 at 16:51
Please! I'm learning as I go right now. Alternatively I could do it by adding multiple search strings I suppose. Whatever is easier. – RichT Mar 20 '15 at 17:44
I'll do the modifications in less than 24h mate, sry for delay – R3uK Mar 22 '15 at 13:49
Great thanks! I was maybe going to try and just loop this multiple times with another macro. – RichT Mar 25 '15 at 18:22
@RichT Is it working properly? If it is, please validate the answer with the tick right under the down vote, so that we can close the subject. If it's not we will help you to get it right. – R3uK Mar 26 '15 at 06:44

Run script on all sheets excel

1 Answers1