Simplify Formatting Macro

[RNoose]

Hope this doesn't bore you with its simplicity, but to me it's uberly complicated at this stage of my vba development. I recorded a macro to do a number of formatting changes to a weekly workbook, but the code is long and cumbersome. I've been reading up on macros, and everything I read says to try to avoid using Select/Activate, but the recorded code is full of those references. I've tried drafting a code from scratch using website references, but can't seem to draft all the formatting steps I get when I record the code. Below is the formatting I'm trying to accomplish. Can anyone point me in the direction of a good macro training guide or advise me how to write this macro from scratch? I've read Excel Vba Programming for Dummies front to back, but still consider myself a vba dummy. But I fully recognize the power in programming, and am eager to learn.

7 sheet wkbk, but formatting will only apply to sheets 1-5. The data on each worksheet varies in size, so at the very least range A:N captures it all on each worksheet.

page orientation - landscape
fit to 1 wide, 10 tall
font size = 8
wrap text
Set No Fill to all cells that currently have white fill
row height = 36.00
column width:
A = 8, B = 3, C = 14, D = 7, E = 14, F = 3, H:K = 10, L = 4, M = 13, N = 30
Find - Replace:
Col A - "Option" replace with "Opt."
Column F - "Location" replace with "Loc." & If State name spelled out - replaced with state abbreviation.

Can something like this be accomplished within the scope of an individual macro, or will I have to piecemeal it with multiple macros?

Windows 7 Pro Sp 1
Excel 2010

 

[par60056]

Starting with the code that the recorder generates is OK. Then you need to cut out all the crud dealing with moving the screen and selecting cells.

If you post a piece of your code that the recorder made for you I can show you how to edit it to simplify.

Just use the CODE tag when you post it.

 

[par60056]

here is a start to what you want. I did not do the find & replaces.

Sub formatSheet()

With ActiveSheet.UsedRange
    .RowHeight = 36
    .WrapText = True
    .Font.Size = 8
End With


Columns(1).ColumnWidth = 8
Columns(2).ColumnWidth = 3
Columns(3).ColumnWidth = 14
Columns(4).ColumnWidth = 7
Columns(5).ColumnWidth = 14
Columns(6).ColumnWidth = 3
Columns(8).ColumnWidth = 10
Columns(9).ColumnWidth = 10
Columns(10).ColumnWidth = 10
Columns(11).ColumnWidth = 10
Columns(12).ColumnWidth = 4
Columns(13).ColumnWidth = 13
Columns(14).ColumnWidth = 30

With ActiveSheet.PageSetup
    .PrintArea = "$A:$N"
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesWide = 1
End With

End Sub

Always do the page setup after all other work is done

 

[RNoose]

Way prettier than the one I cleaned up from the recording, thanks. Are you saying I could do the find/replace functions within the same macro, you just didn't do it due to its difficulty? Or should I try creating that as a separate one altogether?

Does anyone else know how to embed the find/replace function within the above procedure?

 

[par60056]

yes you can do it in the same macro. I just didn't have any data to work with.

If you run the recorder doing the find and replace I can show you how to clean it up.

 

[RNoose]

Here's a snippet.

Columns("G:G").Select
    Selection.Replace What:="State of Mississippi", Replacement:="MS", LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="State of Louisiana", Replacement:="LA", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="State of Texas", Replacement:="TX", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="State of Arkansas", Replacement:="AR", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("A:N").Select
    With Application.FindFormat.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Application.ReplaceFormat.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Replace What:="", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub

 

[Michael M]

Also, if you are setting the column widths to suit the text or values you could change the code to...

With ActiveSheet.UsedRange
    .RowHeight = 36
    .WrapText = True
    .Font.Size = 8
    .Columns.AutoFit
End With

rather than a long list of column width adjustments

 

[RNoose]

Thanks for that tip too.

 

[par60056]

Essentially, anyplace that it does a select and then uses selection.SOMETHING, you can replace the selection with the selected item.

So

Columns("G:G").select
Selection.Replace What:="State of Mississippi", Replacement:="MS", LookAt _        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

with

Columns("G:G").Replace What:="State of Mississippi", Replacement:="MS", LookAt _        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

If you have a lot of replacements on the same data, you could:

with Columns("G:G")
    .Replace What:="State of Mississippi", Replacement:="MS", LookAt _        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
   .Replace What:="State of Louisiana", Replacement:="LA", LookAt:= _        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
end with

 

[par60056]

Autofit can be useful. But sometimes you want more white space between numbers so that it is easier to read. Or sometimes you will want to be able to line things up from 1 run to another and that will be easier if the column widths are constant.

I tend to use autofit when it is a 1 time quick dirty report but if I am making something for long term production I want to specify the column widths.