Trim data in colum A throughout multiple sheets

[bmuum3]

I am trying to get Column A to trim down because of extra spaces in it. I need to do this across 29 sheets and can not get the formula to work.

Here is what I am trying to use




Sub TrimSpaces()

Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.Name <> "Emall" And sh.Name <> "FPDSNG" Then
sh.Activate
Range("A2:A40000").Select

For Each cell In Selection
cell.Value = Trim(cell.Value)
End If
Next sh
Application.ScreenUpdating = True

MsgBox "Data has been updated. "

End Sub




I get a "Compile Error: End If without Block If


Any help would be greatly appreciated.

[NickyC]

you appear to be missing "next cell" before "end if"

[Alf]

Hi bmuum3

Not trying to solve you problem because NickyC did that.

I’m just curious about your range selection i.e. “A2:A40000”.

If you are setting it that big just to be sure you “capture” all values you could instead try to set the range dynamically.

Range(“A2:A” & Range(“A” & Rows.Count).End(xlUp).Row) will go from the bottom and upwards to the first value in the A column. If the first value found is at row 1500 (going from the bottom and upwards) excel will then interpret
Range(“A2:A” & Range(“A” & Rows.Count).End(xlUp).Row) as Range(“A2:A1500”)

So you could rewrite your macro a bit changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Alf

Ps Always wrap your code or the moderators will be upset!

[bmuum3]

Thanks for all the help

Alf, thanks for the heads up.