How do I loop through this macro for cleaner code?

[Rusketeer]

I managed to put together a macro that can group columns based on the cell's selection. I have it based on every month... but I don't know how to make it look compact. Here's what it currently looks like ( just shortened a bit for your sake, I'm not listing every month... )

Dim ws As Worksheet
If ActiveCell.Value = "March-01" Then
    For Each ws In Worksheets
        ws.Select
        Columns("E:O").Group
        Columns("T:AD").Group
        Columns("AJ:AT").Group
    Next ws
End If
If ActiveCell.Value = "April-02" Then
    For Each ws In Worksheets
        ws.Select
        Columns("F:O").Group
        Columns("U:AD").Group
        Columns("AK:AT").Group
    Next ws
End If

Each time a month progresses, the Columns portion shrinks by one column on the left. The months sequence up until January-11 - Februrary-12 is left out as I will just have everything shown and ungrouped by that point. We can work with numbers like 1, 2, 3... instead of March-01 and April-02 for simplicity's sake.

It.. it works as is, but it's not something I would want to look at. I tried to search a while for something to fix this, maybe I'm using the wrong keywords?

 

[Scott Huish]

Perhaps:

Sub test()
Dim ws As Worksheet, t, d As Long
t = Split(ActiveCell.Value, "-")(0)
d = Month(DateValue(t & " 1/0")) - 3
For Each ws In Worksheets
    Range(Range("E:E").Offset(, d), Range("O:O")).Group
    Range(Range("T:T").Offset(, d), Range("AD:AD")).Group
    Range(Range("AJ:AJ").Offset(, d), Range("AT:AT")).Group
Next
End Sub

 

[Rusketeer]

Perhaps:

Sub test()
Dim ws As Worksheet, t, d As Long
t = Split(ActiveCell.Value, "-")(0)
d = Month(DateValue(t & " 1/0")) - 3
For Each ws In Worksheets
    Range(Range("E:E").Offset(, d), Range("O:O")).Group
    Range(Range("T:T").Offset(, d), Range("AD:AD")).Group
    Range(Range("AJ:AJ").Offset(, d), Range("AT:AT")).Group
Next
End Sub

Hi Scott, thanks for the hasty reply ( and sorry for my slow one ).

Unfortunately, that macro 8 groups into one worksheet in the same locations ( edit: to clarify, if I selected March-01 it would do E:O, T:AD, AJ:AT 8 times ). It gave me a run-time error of 1004 on the first range line, saying the group method of Range class failed.

I tried to tamper a little bit to see if the original code could fix this, only two lines were added - this resulted in the first worksheet being grouped twice, as well as the last worksheet. Oh well, but here is what I messed with

Sub test()
Dim ws As Worksheet, t, d As Long
t = Split(ActiveCell.Value, "-")(0)
d = Month(DateValue(t & " 1/0")) - 3
For Each ws In Worksheets
    [B]ws.Select[/B]
    Range(Range("E:E").Offset(, d), Range("O:O")).Group
    Range(Range("T:T").Offset(, d), Range("AD:AD")).Group
    Range(Range("AJ:AJ").Offset(, d), Range("AT:AT")).Group
[B]Next ws[/B]
End Sub

 

[Scott Huish]

Yes, I did forget that part, but that is literal text in your sample right? Not a date formatted that way?

 

[Rusketeer]

If you mean literal text by formatted to be text and I typed in March-01, yes. Should I have them as ="March-01" and so on?

 

[Scott Huish]

No, that it isn't necessary. Can you put this formula in your sheet somewhere and tell me what it returns?

Change the cell in this formula to the cell that contains March-01

=ISNUMBER(A1)

 

[Rusketeer]

Hi Scott, sorry for the long response. I was caught up in a lot of work and was swept away from this file.

It returns as false.

 

[Scott Huish]

With the addition of the ws.Select it doesn't do that for me. Is there anything else you changed in the code? Are you selected on the correct cell when you run the code?

BTW, if it's always the same cell we can hardcode that range vs. using ActiveCell