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

 

[Davavo]

ok thanks, will give that a try

 

[Davavo]

Copy the function from post#2 to a module & then change this line

Sheets.Add.Name = a(p, 1)

to

   Sheets.Add.Name = ValidWBName(a(p, 1))

That way you don't need to change the values in the sheet.

ok thanks, will give that a try

 

[Davavo]

Copy the function from post#2 to a module & then change this line

Sheets.Add.Name = a(p, 1)

to

   Sheets.Add.Name = ValidWBName(a(p, 1))

That way you don't need to change the values in the sheet.

i get a compile error "by ref argument type mismatch" on that line highlighting the letter 'a'

 

[Fluff]

Oops, it should be

   Sheets.Add.Name = ValidWBName(CStr(a(p, 1)))

 

[Davavo]

Oops, it should be

   Sheets.Add.Name = ValidWBName(CStr(a(p, 1)))

I am now getting an application defined or object defined error.
I think it is something to do with tha data rather than the code
It is creating all of the new sheets, but is also creating a sheet with all of the values in and one with no values in (or it is freezing before it fills it)
Perhaps that column need to be cleaned and trimmed in advance. it is contiguous but the cells contain some spaces at the end. Perhaps there is something else.

 

[Fluff]

Ok, how about replacing you code in post#10 with

   Dim Ary As Variant
   Ary = Array("\", "/", ",", ".", ":", ";")
   For i = 0 To UBound(Ary)
      Range("I:I").Replace Ary(i), "_", xlPart, , , , False, False
   Next i

 

[Davavo]

Ok, how about replacing you code in post#10 with

   Dim Ary As Variant
   Ary = Array("\", "/", ",", ".", ":", ";")
   For i = 0 To UBound(Ary)
      Range("I:I").Replace Ary(i), "_", xlPart, , , , False, False
   Next i

Thats nice, yea, works fine thanks.

Rather than clean and trim the column i just used the above and replaced with "" rather than "_".

The error i mentioned previously was caused by the totals row. The Object defined error. That as caused by the totals row.

I really appreciate your help once again Fluff.
My job isn't supposed to be about this, so that why I have a client and yet, clearly don't know how to do this.
I am picking it up as I go, but I feel that I am often using things and not fully understanding how it is working. Can you recommend some reading? A book? By the end of the day I am screen blind.

Thanks again.

 

[Fluff]

You're welcome & thanks for the feedback.

I've never read any books on Excel/VBA so cannot recommend anything personally, but It's worth looking at anything by Bill Jelen (aka MrExcel) or John Walkenbach

 

[Davavo]

You're welcome & thanks for the feedback.

I've never read any books on Excel/VBA so cannot recommend anything personally, but It's worth looking at anything by Bill Jelen (aka MrExcel) or John Walkenbach

Thanks

 

[Fluff]

My pleasure