CODEWITHSUNDEEP
vbaloops
Pedro Celso
Pedro Celso

Reputation: 1

Loop by non blank and blank cells

good morning! Im learning VBA and saw the code that Head of Catering put here How to perform a sumif using blank cells as a reference? and it works fine, but in the inverse procedure that i need to implement. In my spreadsheet the counting starts in the first non blank cell and goes throghout the blank cells until the next non blank cell is reached, and then input the sum in the last blank cell (sum A cells from the first non blank until the last blank cell and then start again from the next non blank cell). Thanks 

00:21.6     10/1/2012 1:43      FALSE       00:21.6
01:47.7     10/1/2012 2:13      FALSE       01:47.7
00:56.7     10/1/2012 2:49      FALSE       
00:54.9     10/1/2012 3:43      
00:11.8     10/1/2012 3:43              2:34(00:56.7 +00:54.9+00:11.8)
02:10.9     10/1/2012 3:46      FALSE       02:10.9     
01:05.4     10/1/2012 3:58      FALSE       
00:55.8     10/1/2012 4:53      
04:41.8     10/1/2012 4:52      
00:26.3     10/1/2012 4:58      
00:04.2     10/1/2012 4:58      
00:15.3     10/1/2012 4:59      
00:06.4     10/1/2012 4:57      
00:10.7     10/1/2012 4:56      
00:04.4     10/1/2012 4:56      
00:04.2     10/1/2012 4:57      
00:29.2     10/1/2012 4:57      
00:34.5     10/1/2012 4:56      
01:22.4     10/1/2012 4:55                       (01:05.400:55.8+04:41.8+...+01:22.4)

00:08.1     10/1/2012 4:55      FALSE       00:08.1
03:20.9     10/1/2012 4:51      FALSE       03:20.9
00:56.3     10/1/2012 5:42      FALSE       00:56.3
02:23.1     10/1/2012 5:51      
01:20.6     10/1/2012 5:48      
00:09.8     10/1/2012 5:49      FALSE       03:53.5(i.e., 02:23.1+01:20.6+00:09.8)
01:40.0     10/1/2012 7:47      FALSE       01:40.0
01:13.4     10/1/2012 8:11      FALSE       01:13.4
00:41.6     10/1/2012 9:49      FALSE       00:41.6
01:08.1     10/1/2012 11:56     FALSE       01:08.1

Upvotes: 0

Views: 306

Answers (1)

Skip Intro
Skip Intro

Reputation: 860

This code walks down the column, adding as it goes and then puts the answer into the first blank cell it finds.

Sub CountMeBlank()

Dim varCounter As Variant

Do Until ActiveCell.Value = ""

    varCounter = varCounter + ActiveCell.Value
    ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Value = varCounter


'Grabs the formatting from the last cell as the data could be time or numeric.
ActiveCell.Offset(-1, 0).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Then colours the cell red to show contrast
ActiveCell.Font.Color = vbRed

End Sub

You'll need to wrap that in some form of loop if you need it to walk through all of the data at once.

Upvotes: 0

Related Questions