Forum Discussion

RoryL1045's avatar
RoryL1045
Copper Contributor
Jun 13, 2022

Excel VBA adding titles to Subtotalled Information under second view in First Visible Cell

 
 
In my excel file, I use a subtotal to sort large quantity of information, Normally, when doing this manually, I select the second view then in the first row, use = and the column above. For example, if the first Visible Cell is 15, then in A15, I add in = A14, in B15 I add in B14, in C15 I add in C14, in D15 I add in D14. Then I copy the first row (A-D) and select the whole subtotalled units in the sheet, select visible cells only and copy and paste special formulas to see the subtotalled information.
 
 
The Series of Images below shows the process of doing this entirely manually, but I have been trying to find a macro that will add the subtotal titles.

 

 

The one I tried is meant to find the first blank cell in row A, but it is finding A15 every time instead of the first visible cell. This will not be A15 every time, as it will be different information every week so different subtotals.

 

 

 

Sub MacroAddingTitles()
   '
   ' MacroAddingTitles Macro
   ' Adding Titles to Subtotals
   '

   '
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("A15").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=3
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=R[-1]C"
    Range("A16").Select
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("A15").Select
    Selection.Copy
    Range("B15:D15").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Any Suggestions?

excel
Macros and VBA

1 Reply

  • Charla74's avatar
    Charla74
    Iron Contributor
    Jun 13, 2022
    You could try formatting the whole data set as a table whereby formulas would automatically be entered into the rows below (assuming that the values brought down from row 14 are formulas). Select entire data range (select any cell within data and use shortcut Ctrl+A, then use shortcut Ctrl+T to format as a table - check the box for 'My Data Has Headers'). Hope this helps.

Resources