Macro Hides columns During Execution

[Houstonwolf]

Hello. I created the following macro using the recorder. I have made no modifications. It works the way I want until I save it in PERSONAL.xls and close Excel. The next time it runs, it hides all the columns except for L & M. I say it hides them, but I can't unhide them.

The first time it happened, I deleted the macro and rerecorded it exactly the same way. Again, it works just fine until I close Excel and then re-open and try to use it again. Any suggestions as to what I'm doing wrong or what mods I need to make so it will run correctly each time?

Sub StoreDoorSetup()
'
' StoreDoorSetup Macro
' Macro recorded 9/14/2006 by Houston Wolf
'
' Keyboard Shortcut: Ctrl+m
'
Columns("A:A").Select
Range("A2").Activate
Selection.EntireColumn.Hidden = True
Columns("C:F").Select
Range("C2").Activate
Selection.EntireColumn.Hidden = True
Columns("L:M").Select
Selection.ClearContents
Range("A1:K1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("J3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1:K86").Sort Key1:=Range("J3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("L2").Select
Selection.ClearComments
Range("L2").Select
ActiveCell.FormulaR1C1 = "SKU"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("M2").Select
ActiveCell.FormulaR1C1 = "PO"
Columns("B:M").Select
ActiveSheet.PageSetup.PrintArea = "$B:$M"
End Sub

 

[erik.van.geit]

Hi,

here is your code cleaned up
it will do exactly the same as your code

Option Explicit

Sub StoreDoorSetup()
'
' StoreDoorSetup Macro
' Macro recorded 9/14/2006 by Houston Wolf
'
' Keyboard Shortcut: Ctrl+m
'
Columns("A:A").EntireColumn.Hidden = True
Columns("C:F").EntireColumn.Hidden = True
Columns("L:M").ClearContents

    With Range("A1:K1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    End With

Range("A1:K86").Sort Key1:=Range("J3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

    With Range("L2")
    .ClearComments
    .FormulaR1C1 = "SKU" 'could be written as .Value = "SKU"
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold"
    .Font.Size = 8
    End With

Range("M2") = "PO"
ActiveSheet.PageSetup.PrintArea = "$B:$M"

End Sub

this is not a solution to your problem but a way to see more clearly what it does: it's almost plain english now

for me columns A, C, D, E, F are hidden
B, G, H, I, J, K, L, M and all other columns are still visible

ready to help more if needed

kind regards,
Erik

EDIT: when posting code use the CODE-tags above your message body which appears when using "post reply"button

 

[Houstonwolf]

It is quite a bit more clear... and shorter too. I see what you're doing. There's so much extraneous code when using the recorder.

One thing I forgot is code to unmerge Row 1. How would I add that?

 

[erik.van.geit]

would it help to reply with an "invitation to try out yourself" ?
recording is easy and
it's quite sure you will be able to edit it with the knowledge received in previous post

I promise to check this post to help further if needed :wink:
if you think I forgot, throw me a PM

 

[Houstonwolf]

I did record the macro I wanted (to unmerge Row 1) but when I copied it in with your code, it deleted the text from Row 2, Col A:K and moved the rows below up one (which throws off the data, of course). So I just started over. But at least now I got a crash course in macro editing for clarity and brevity!

Thanks again, Erik.

 

[erik.van.geit]

Hi,

most of the "heavy-users" hate merged cells
I cannot reproduce your problem

recorded

Sub Macro1()
'
' Macro1 Macro
' De macro is opgenomen op 18/09/2006 door Erik.
'

'
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

relevant part only

Sub Macro1()
    Rows(1).MergeCells = False
End Sub

always try out code on a new sheet
merge cells "B1:E1"
run the code
cell will be unmerged
now merge "B1:E5"
run code ...

next step: try to apply it to your worksheet ...

best regards,
Erik

 

[Houstonwolf]

I went from this:

Sub StoreDoorSetup()
'
' StoreDoorSetup Macro
' Macro recorded 9/18/2006 by Houston Wolf
'
' Keyboard Shortcut: Ctrl+m
'
    Rows("1:1").Select
    With Selection
        .VerticalAlignment = xlBottom
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Columns("B:E").Select
    Selection.Delete Shift:=xlToLeft
    Columns("G:H").Select
    Selection.ClearContents
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "PO"
    With ActiveCell.Characters(Start:=1, Length:=2).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "SKU"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "PO"
    Columns("A:H").Select
    ActiveSheet.PageSetup.PrintArea = "$A:$H"
End Sub

to this:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 9/18/2006 by Houston Wolf
'
    Rows(1).MergeCells = False
    Columns("A").Delete Shift:=xlToLeft
    Columns("B:E").Delete Shift:=xlToLeft
    Columns("G:H").ClearContents
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "SKU"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 8
    End With
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "PO"
    Columns("A:H").Select
    ActiveSheet.PageSetup.PrintArea = "$A:$H"
End Sub

Much cleaner! And it works!

And I imagine it could be even cleaner thatn this...

 

[erik.van.geit]

yes,

clean up the selections
range.select
selection."action"

range."action"

this will be more readable
it will run faster (perhaps not a big deal for the moment, but it's good practice)
it will not flicker the screen

about this last point
start code with

Application.ScreenUpdating = False

end with

Application.ScreenUpdating = True

last experiment
type
Range("A1")
now add a DOT
a list will popup with all kinds of functions and properties
example
Range("A1").Address
could be used like

MsgBox Range("A1").Address

this is not quite useful, but it's just for the example

greetings from Belgium,
Erik

 

[Houstonwolf]

Dank u opnieuw voor uw hulp, Erik!

 

[erik.van.geit]

Dank u opnieuw voor uw hulp, Erik!

nice, dutch speaking in USA ?
or cheating with translator-machines