Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

DW Simpson Global Actuarial & Analytics Recruitment
Download our 2017 Actuarial Salary Survey
now with state-by-state salary information!


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 09-04-2018, 07:17 PM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 35,202
Default Excel VBA - ungroup & group

I inherited a spreadsheet...I'm not at liberty to make changes, so I want to introduce some vba to make it easier to update the thing.

Columns A & B are descriptive.
Columns C through AH are grouped are "hidden" so that only column AH shows.
Columns AI through AS are shown.

My job every month is to re-group the columns to the left (now C-AH) with an additional column so that columns C-AI are now grouped (and hidden) and only column AI shows (along with AJ through AS as before...new data will go into AT).

Do you have any ideas how I could set up a macro to do that work for me? (I have to do this on numerous sheets.)

---

What I do now is, with the cursor on cell AH5 (where the rows & columns are frozen)...
alt-d-g-s (data group show)
alt-d-g-u-c (data group ungroup columns)
shift-home (highlights from current column to column C (because the panes are actually frozen there))
alt-d-g-g-c (data group group columns)
alt-d-g-h (data group hide)


...but that gets old after about the second time.

Last edited by 1695814; 09-05-2018 at 11:09 AM..
Reply With Quote
  #2  
Old 09-04-2018, 09:28 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 85,032
Blog Entries: 6
Default

I recommend getting started by trying the macro recorder.
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #3  
Old 09-05-2018, 11:09 AM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 35,202
Default

Quote:
Originally Posted by campbell View Post
I recommend getting started by trying the macro recorder.
Oh, I've tried that.

Here:
Code:
Sub Macro8()
    Range(Selection, Cells(ActiveCell.Row, 1)).Select
    Selection.Columns.Ungroup
    Selection.Columns.Group
End Sub
I probably need to do something about " Range(Selection, Cells(ActiveCell.Row, 1)).Select" because when I rerun it, it ends up grouping beginning at Col A instead of Col C.

Last edited by 1695814; 09-05-2018 at 11:16 AM..
Reply With Quote
  #4  
Old 09-05-2018, 11:24 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 85,032
Blog Entries: 6
Default

Ah, yes. The macro recorder is good for getting started, but then you need to alter the code.

You can use Range references like you do in regular Excel cell formulas:

Range("C:AH").Columns.Ungroup
or
Range("C:AI").Columns.Group
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #5  
Old 09-05-2018, 11:28 AM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 85,032
Blog Entries: 6
Default

And if it's multiple tabs, you can set up a loop for it to go through all the sheets in the file.

Code:
Dim wksht at Worksheet

 For Each wksht in ThisWorkbook.Worksheets
          wksht.Range("C:AH").Columns.Ungroup
          wksht.Range("C:AI").Columns.Group
Next wksht
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #6  
Old 09-05-2018, 11:53 AM
1695814's Avatar
1695814 1695814 is offline
Member
SOA AAA
 
Join Date: Aug 2002
Studying for nothing. I quit.
Favorite beer: Root
Posts: 35,202
Default

Quote:
Originally Posted by campbell View Post
Ah, yes. The macro recorder is good for getting started, but then you need to alter the code.

You can use Range references like you do in regular Excel cell formulas:

Range("C:AH").Columns.Ungroup
or
Range("C:AI").Columns.Group
Col C will always be the leftmost column, but next month the rightmost columns will be AI & AJ...and the month after that it'll be AJ & AK...etc.

There must be a way to accommodate that.
Reply With Quote
  #7  
Old 09-05-2018, 12:09 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 85,032
Blog Entries: 6
Default

Well, the easy for-now fix would be to just change a few characters in the code each month and run it.

The next level step would be to have the month number somewhere as a constant, like this:

Code:
Public Const CurrMonth as Integer = 32

Public Sub UpdateGroups()

 For Each wksht in ThisWorkbook.Worksheets
          wksht.Range(Columns(3),Columns(CurrMonth+2)).Columns.Ungroup
          wksht.Range(Columns(3),Columns(CurrMonth+3)).Columns.Group
Next wksht



End Sub
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #8  
Old 09-05-2018, 12:10 PM
campbell's Avatar
campbell campbell is offline
Mary Pat Campbell
SOA AAA
 
Join Date: Nov 2003
Location: NY
Studying for duolingo and coursera
Favorite beer: Murphy's Irish Stout
Posts: 85,032
Blog Entries: 6
Default

Next level after that, you could calculate the month from the current date...

but that's a googling exercise left to the reader
__________________
It's STUMP

LinkedIn Profile
Reply With Quote
  #9  
Old 09-05-2018, 12:14 PM
ALivelySedative's Avatar
ALivelySedative ALivelySedative is online now
Member
CAS
 
Join Date: Dec 2013
Location: Land of the Pine
College: UNC-Chapel Hill Alum
Favorite beer: Red Oak
Posts: 2,762
Default

Quote:
Originally Posted by campbell View Post
Well, the easy for-now fix would be to just change a few characters in the code each month and run it.

The next level step would be to have the month number somewhere as a constant, like this:

Code:
Public Const CurrMonth as Integer = 32

Public Sub UpdateGroups()

 For Each wksht in ThisWorkbook.Worksheets
          wksht.Range(Columns(3),Columns(CurrMonth+2)).Columns.Ungroup
          wksht.Range(Columns(3),Columns(CurrMonth+3)).Columns.Group
Next wksht



End Sub
Disclosure: don't know much of anything about VBA.

In general though, set your 'integer' as a datetime function pulling the month/year out of the sysdate and calculate the number of months from some base date of your choosing.

edit: ninja me this, ninja me that...
Reply With Quote
  #10  
Old 09-05-2018, 06:15 PM
Knoath Knoath is offline
Member
CAS
 
Join Date: Oct 2015
Posts: 54
Default

You could declare a variable named, for example, LastCol, then find the last column for which the hidden property is true. Set LastCol to that number, unhide and ungroup your columns, then rehide and regroup the columns up until LastCol + 1.

You just have to make sure you don't run the same macro twice in one month.
Reply With Quote
Reply

Tags
excel, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 01:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.42554 seconds with 9 queries