Macro Hides columns During Execution

Houstonwolf

Board Regular
Joined
Jul 28, 2006
Messages
154
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

here is your code cleaned up
it will do exactly the same as your code
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

Board Regular
Joined
Jul 28, 2006
Messages
154
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Jul 28, 2006
Messages
154

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

recorded
Code:
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
Code:
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

Board Regular
Joined
Jul 28, 2006
Messages
154

ADVERTISEMENT

I went from this:

Code:
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:

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
Application.ScreenUpdating = False
end with
Code:
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
Code:
MsgBox Range("A1").Address
this is not quite useful, but it's just for the example

greetings from Belgium,
Erik
 

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top