Need my Macro to replace words with abbreviations

[ahetzel10]

Hi all,

I am running a macro to delete and hide some columns of weekly data to quickly create a sheet small enough to print out. I want the macro to automatically replace a couple phrases such as "single family" with "SF", and "new construction" with "NC". How can I incorporate this into my Macro?

Thanks!

 

[Fluff]

Hi & welcome to the board
How about

Sub Abbreviate()
   With Columns(1)
      .Replace "single family", "SF", xlPart, , False, , False, False
      .Replace "new construction", "NC", xlPart, , False, , False, False
   End With
End Sub

Changing the column to suit

 

[ahetzel10]

I appreciate the quick help! Now do I just edit the existing code and place this at the end?

 

[Fluff]

Just copy the code (without the sub & End sub lines) & paste it into your existing code.

 

[ahetzel10]

Ok this is what my macro produces for me. Becuase of deleted and hidden columns, "Project type" is column G but is the fifth column from the left.

This is what the end of my attempted code currently looks like with your addition. Not sure what I am doing wrong. Sorry I don't know much about coding...

With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Range("C1").Select
ActiveCell.FormulaR1C1 = "Permit#"
Columns("H:H").Select
Selection.Copy
Range("H1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ProjectCity"
Range("H2").Select
With Columns(5)
.Replace "Single Family (Dev.Only)", "SF", xlPart, , False, , False, False
.Replace "new construction", "NC", xlPart, , False, , False, False
End With
End Sub

Permit#	PermitDate	ProjectAddr	ProjectName	ProjectType	ProjectCity	Company	Phone	Size	Value	WorkType	County
1	9/7/2017	808 CASTILLA WY	SUTHERLAND	Single Family (Dev.Only)	WINDER	LGI HOMES	855-441-6300		115596	New Construction	Barrow
1	10/23/2017	426 RENOWN CT	SUTHERLAND	Single Family (Dev.Only)	WINDER	LGI HOMES	855-441-6300		159626	New Construction	Barrow
10	9/24/2017	1222 DIANNE DR	SUTHERLAND	Single Family (Dev.Only)	WINDER	LGI HOMES	855-441-6300		116530	New Construction	Barrow
10	10/12/2017	2006 MASSEY LN	PINNACLE @ YARGO BLD	Single Family (Dev.Only)	WINDER	VISION HOME CONSTRUCTION, INC.	404-569-4217		130546	New Construction	Barrow
11	9/24/2017	1220 DIANNE DR	SUTHERLAND	Single Family (Dev.Only)	WINDER	LGI HOMES	855-441-6300		115595	New Construction	Barrow
11	10/12/2017	2008 MASSEY LN	PINNACLE @ YARGO BLD	Single Family (Dev.Only)	WINDER	VISION HOME CONSTRUCTION, INC.	404-569-4217		127269	New Construction	Barrow
12	9/24/2017	1218 DIANNE DR	SUTHERLAND	Single Family (Dev.Only)	WINDER	LGI HOMES	855-441-6300		116530	New Construction	Barrow
12	10/12/2017	2010 MASSEY LN	PINNACLE @ YARGO BLD	Single Family (Dev.Only)	WINDER	VISION HOME CONSTRUCTION, INC.	404-569-4217		131742	New Construction	Barrow
13	9/24/2017	1216 DIANNE DR	SUTHERLAND	Single Family (Dev.Only)	WINDER	LGI HOMES	855-441-6300		115595	New Construction	Barrow

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[ahetzel10]

Hi, I posted some data on here and it said it needed your approval and it disappeared. I can't get the code to work so I was trying to show you whats going on.

 

[Joe4]

Hi, I posted some data on here and it said it needed your approval and it disappeared.

That is just telling you that it needs Moderator approval before you will be able to see it.
I approved it and you can see it now.

 

[Fluff]

Change Columns(5) to Columns(7)
Col G is the 7th column regardless of hidden columns.

 

[ahetzel10]

Worked! Thank you so much. This will be a big help.

 

[Fluff]

Glad to help & thanks for the feedback