Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
I have posted this again to hope that someone will give me further assistance
- I "ticked" the last post because it did work and well, but I realised what I actually need is slightly different. I asked for some coding that would search for each blank cell in a column and subtotal below it until the next blank cell. Stefi gave me this: Sub stotal() Range("S1").End(xlDown).Activate Range("S" & ActiveCell.Row - 1).Activate Do While ActiveCell.Row < Range("S" & Rows.Count).End(xlUp).Row + 1 stotstart = ActiveCell.Row + 1 stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row ActiveCell.Formula = "=SUBTOTAL(9,S" & stotstart & ":S" & stotend & ")" Range("S" & stotend + 1).Activate Loop End Sub BUT... what I realised when I put it into play was that what I really need was it to subtotal when both S and T columns are both empty at the same time, because there are odd cells that are empty but are not a sub total row. It is only those rows that have both S and T columns (the total columns) empty that are the subtotal columns. I was guessing something like.... Range("S1").End(xlDown).Activate Range("S" & ActiveCell.Row - 1).Activate Do While ActiveCell.Row < Range("S:T" & Rows.Count).End(xlUp).Row + 1 stotstart = ActiveCell.Row + 1 stotend = Range("S" & ActiveCell.Row + 1).End(xlDown).Row with selection.font .font = 11 .bold = true ActiveCell.Formula = "=subtotal(9,S" & stotstart & ":S" & stotend & ")" Range("S" & stotend + 1).Activate Nextstotstart = ActiveCell.Row + 1, ActiveCell.column +1 Nextstotend = Range("T" & ActiveCell.Row + 1).End(xlDown).Row ActiveCell.Formula = "=subtotal(9,T" & nextstotstart & ":T" & nextstotend & ")" Range("S" & stotend + 1).Activate Loop What are your thoughts??? I really don't understand coding as well as I would like so I am just having a stab in the dark. Cheers Gai |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... | Excel Programming | |||
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste | Excel Programming | |||
Copy to first Blank cell in Colum C Non blank cells still exist be | Excel Programming | |||
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. | Excel Discussion (Misc queries) |