Using VBA Excel I am trying to count the number of blank cells in a column between entries

Question

I would like to determine the number of blank cells in a column between non-blank cells. I have an excel spreadsheet of the daily closing prices of a stock over the last year. Each row in excel lists the daily closing prices in chronological order. The spreadsheet lists the day I bought the stock and the day I sold the stock on the relevant row. A specific column is used to record the date. An extract of the spreadsheet is contained below.

  Stock       Date       ClosingPrice    Entered/Exited    DaysHeld?   
 -------- ------------- --------------- ----------------- ------------ 
  Apple    01/02/2017          116.25    Bought                        
  Apple    01/03/2017          119.75                                  
  Apple    01/04/2017          117.50                                  
  Apple    01/05/2017          118.75    Sold

I am trying the determine the number of blank cells between the 'Entered' row to the 'Exited' row under column heading Entered/Exited using a loop in VBA excel as I trade many stocks and copying and pasting an excel formula every time has become time consuming.

The compiled the following code which counts the number of blank cells, however it always returns the value 1. In the code below, cell AI56 is the day I bought the stock, 'bought' under column heading 'Entered/Exited'

Sub SandpitCountBlankCells()

' SandpitCoutBlankCells Macro
' Macro calculates the number of blank spaces
'

'Step 1: Declare variables
 Dim CountBlankCells As Long
 CountBlankCells = 0

'Select Cell AI56
Range("AI56").Select
ActiveCell.Offset(-1, -1).Range("A1").Select

'Check wheher the cell in the previous row is blank
If ActiveCell.Value = Blank Then
CountBlankCells = CountBlanksCells + 1

'While loop to check whether cells in the previous rows are blank
While ActiveCell.Value = Blank
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveCell.Select
CountBlankCells = CountBlanksCells + 1

'Place the value of the number of blank cells in cell AL56
Range("AJ56").Value = CountBlankCells

'Exit the loop
Wend

Range("AJ56").Value = CountBlankCells


End If


End Sub

[How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and look these questions: [Count rows between non-empty cells](https://stackoverflow.com/questions/7924909/count-rows-between-non-empty-cells) and [Count number of blank cells in row between last cell and next non-blank cell](https://stackoverflow.com/questions/18988648/count-number-of-blank-cells-in-row-between-last-cell-and-next-non-blank-cell) — danieltakeshi, Aug 28 '17 at 13:44

score 0 · Accepted Answer · answered Aug 28 '17 at 13:41

0

When you put:

 Option Explicit

at the top of you vba module you will see that there is a type error and CountBlanksCells has not been declared:

 CountBlankCells = CountBlanksCells + 1

should be:

 CountBlankCells = CountBlankCells + 1

Also the compliler doesn't like 'Blank' instead use:

  While ActiveCell.Value = Empty

answered Aug 28 '17 at 13:41

user500099

964
9
9

Thanks for the heads up. I updated my code and it is running smoothly. – Lawrence Horner Sep 03 '17 at 14:09

score 0 · Answer 2 · answered Aug 28 '17 at 13:44

You're better off avoiding using Select and ActiveCell whenever possible (see VBA Best Practices in Documentation). This method will loop through everything in Column AI and spit out the blanks for each instance, instead of just the one at AI56.

Sub CountDays()
Dim ws As Worksheet
Dim x As Long, y As Long
Dim entExtCol As Long, daysHeldCol As Long, startRow As Long, endRow As Long

On Error GoTo ErrHandler

Set ws = ActiveSheet
entExtCol = 35 'Col AI
daysHeldCol = 36 'Col AJ
startRow = 2 'start of data
endRow = ws.Cells(ws.Rows.Count, entExtCol).End(xlUp).Row 'end of data

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For x = startRow To endRow
    If ws.Cells(x, entExtCol) = "Bought" Then
        ct = 0
        For y = x To endRow
            If ws.Cells(y, entExtCol) = "Sold" Then
                ws.Cells(x, daysHeldCol) = ct
                Exit For
            Else
                ct = ct + 1
            End If
        Next y
    End If
Next x

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

ErrHandler:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

If you're satisfied with an answer, consider 'Accepting' it to help other users with the same issue find the solution as well: see [How does accepting an answer work?](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). — barvobot, Aug 28 '17 at 17:52

Using VBA Excel I am trying to count the number of blank cells in a column between entries

2 Answers2