PDA

View Full Version : [SOLVED] Search repated word in excel using VBA



loveguy1977
12-01-2013, 07:10 AM
Dear, Sir,

I need to autofited some columns that its postion not constant. I wrote "Cln_Autofit" after the last cell in the column. Now i need to print these columns but i need to refit them automaticly.
So i need to VBA to seach for "Cln_Autofit" then i will use Columns(ActiveCell.Column).AutoFit
go next to find "Cln_Autofit"
and so on for all "Cln_Autofit" in the sheet

I can write like this "Cln_Autofit_01" for the first column then "Cln_Autofit_02" for the 2nd and so one then i will search for each "Cln_Autofit_01" & "Cln_Autofit_02" seperatly but this could make the VBA long

Thank you

GarysStudent
12-02-2013, 09:50 AM
How about:


Sub marine()
Dim r As Range, rFit As Range
For Each r In ActiveSheet.UsedRange
If r.Value = "Cln_Autofit" Then
If rFit Is Nothing Then
Set rFit = r
Else
Set rFit = Union(rFit, r)
End If
End If
Next r
rFit.EntireColumn.AutoFit
End Sub

loveguy1977
12-02-2013, 02:18 PM
Thank you. Great