LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 20
Default sub tot column to next blank cell when blank cell exists in both c

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average Formula to display blank cell if named range is blank Rachael F Excel Worksheet Functions 3 February 22nd 08 05:05 PM
Start Cell B1 then find first blank cell, insert subtotal, next non blank, then next blank, sutotal cells in between......... [email protected][_2_] Excel Programming 2 June 7th 07 09:27 PM
Need macro to check if cell is not blank & previous cell is blank, copy information from row above & paste JenIT Excel Programming 4 April 12th 07 08:56 PM
Copy to first Blank cell in Colum C Non blank cells still exist be Ulrik loves horses Excel Programming 2 October 8th 06 07:35 PM
COPY A CONCATENATE CELL TO BLANK CELL PUTTING IN THE NEXT BLANK C. QUEST41067 Excel Discussion (Misc queries) 1 January 15th 05 09:29 PM


All times are GMT +1. The time now is 07:41 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"