Find and Replace any characters in a list of characters

[Davavo]

Hi,
i could really use some help with this.

i have this code that checks a column for an illegal character (for sheet name) and replaces it with "_".
I want it to check for the fill list of characters that are not allowable sheet names

 .Pattern = "[\<\>\*\\\/\?|]"

Sub ReplaceIllegalCharacters()    'this code searches column "I" for "\" and replaces it with "_"

    Columns("I").Replace What:="\", _
                            Replacement:="_", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
End Sub

Additionally, since the script is already going through the same column to check for blanks and delete empty rows, i thought it would be most efficient to combine it with this other function...

'This code looks at column I for blanks and deletes the entire row were it finds them.

For j = Cells(Rows.Count, "I").End(xlUp).Row To 1 Step -1    On Error Resume Next
    
    If Cells(j, "I") = "" Then Cells(j, "I").EntireRow.Delete xlUp
    Next j

Thanks for any help

 

[Peter_SSs]

Don't know if it is possible with your data to end up with long sheet names but you might want to consider a Left(.... , 31) with your function that creates valid sheet names as text length is also a restriction with them.

 

[Davavo]

Don't know if it is possible with your data to end up with long sheet names but you might want to consider a Left(.... , 31) with your function that creates valid sheet names as text length is also a restriction with them.

Hi Peter, thanks for the suggestion. Good call, i will add that in, thanks.

 

[Peter_SSs]

Hi Peter, thanks for the suggestion. Good call, i will add that in, thanks.

Cheers.

Of course in theory that could still cause further problems if two of your cell values were, say, ..
This is an overly long sheet name 1
This is an overly long sheet name 2
.. because, although different, the left 31 characters are the same.