The headline is misleading or is incomplete at best. I agree. You will, too, after you have gone through this post. As bland as it may seem at first, you might find this a tad interesting for the application it was designed and intended to perform.
Microsoft Excel VBA macro programming is a powerful way to experience how data can be manipulated and viewed.
I was given this assignment to find out number of blank cells between two non-blank cells within a range in a spreadsheet. For example, if A1 was a non-blank cell and A4 was the same, too, then B1 should have a display of 2 to indicate that there are two cells that are blank in between.
Following is the full extract of the macro that I wrote to accomplish the task at hand. In case you need help, please do not hesitate to reach out to me.
Function CheckBlanks(CheckRange As Range)
'Created: Satadru Sengupta.
'Dated: 18-12-2019
Evaluate "CheckBlanksSub(" & CheckRange.Address(False, False) & ")"
CheckBlanks = "Difference of blanks created"
MsgBox ("Range difference created. You may delete the formula / user-defined function used.")
End Function
Private Sub CheckBlanksSub(CheckRange As Range)
Dim getRowNum As Integer
Dim getColNum As Integer
Dim initiatePointRow As Integer
Dim endPointRow As Integer
Dim differenceRows As Integer
getRowNum = CheckRange.Row
getColNum = CheckRange.Column
initiatePointRow = 1
differenceRows = 0
initiatePointRow = getRowNum
endPointRow = initiatePointRow
For row1 = 2 To CheckRange.Columns(1).Cells.Count
If CheckRange.Cells(row1, 1).Value = "" Then
differenceRows = differenceRows + 1
Else
ActiveSheet.Cells(endPointRow, getColNum + 1).Value = differenceRows
endPointRow = getRowNum + row1 - 1
differenceRows = 0
End If
Next
End Sub
Microsoft Excel VBA macro programming is a powerful way to experience how data can be manipulated and viewed.
I was given this assignment to find out number of blank cells between two non-blank cells within a range in a spreadsheet. For example, if A1 was a non-blank cell and A4 was the same, too, then B1 should have a display of 2 to indicate that there are two cells that are blank in between.
Following is the full extract of the macro that I wrote to accomplish the task at hand. In case you need help, please do not hesitate to reach out to me.
Function CheckBlanks(CheckRange As Range)
'Created: Satadru Sengupta.
'Dated: 18-12-2019
Evaluate "CheckBlanksSub(" & CheckRange.Address(False, False) & ")"
CheckBlanks = "Difference of blanks created"
MsgBox ("Range difference created. You may delete the formula / user-defined function used.")
End Function
Private Sub CheckBlanksSub(CheckRange As Range)
Dim getRowNum As Integer
Dim getColNum As Integer
Dim initiatePointRow As Integer
Dim endPointRow As Integer
Dim differenceRows As Integer
getRowNum = CheckRange.Row
getColNum = CheckRange.Column
initiatePointRow = 1
differenceRows = 0
initiatePointRow = getRowNum
endPointRow = initiatePointRow
For row1 = 2 To CheckRange.Columns(1).Cells.Count
If CheckRange.Cells(row1, 1).Value = "" Then
differenceRows = differenceRows + 1
Else
ActiveSheet.Cells(endPointRow, getColNum + 1).Value = differenceRows
endPointRow = getRowNum + row1 - 1
differenceRows = 0
End If
Next
End Sub
