Hi, I would like a macro to count the number of blank cells in column B divide that by three and place the result as #Meters 3 cells down from last cell with data.
Example: (also the column could have more or less data with more or less blanks.
<tbody>
</tbody>*The Formula for the Cell with # Meters I want it to use blank rows as the qualifier, but because there is a constant of 2 blanks I would like the divide the total of blanks by 2 to return the proper # of Meters. Each string of continuous data represents 1 meter.
This is the Macro I have tried to alter and use from a previous example, but have not had any luck with my limited skill.
Sub NumberofMeters()
Dim r As Range, rAbove As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Set r = ActiveCell
ActiveCell = Range("B:B").End(xlUp).Offset(2, 0)
Set rAbove = ActiveSheet.Range("B2:" & ActiveSheet.Range("b65536"). _
End(xlUp).Offset(2,0).Address).Select
r.Value = wf.CountBlank(rAbove) / 2
End Sub
Any help would be greatly appreciated!
Example: (also the column could have more or less data with more or less blanks.
A | B | C |
XYZ | ||
XYZ | ||
XYZ | ||
XYZ | ||
Blank | ||
Blank | ||
XYZ | ||
XYZ | ||
XYZ | ||
Blank | ||
Blank | ||
XYZ | ||
XYZ | ||
XYZ | ||
XYZ | ||
XYZ | ||
XYZ | ||
XYZ | ||
Blank | ||
Blank | ||
XYZ | ||
XYZ | ||
XYZ | ||
XYZ | ||
Blank | ||
Blank | ||
4 Meters | ||
<tbody>
</tbody>
This is the Macro I have tried to alter and use from a previous example, but have not had any luck with my limited skill.
Sub NumberofMeters()
Dim r As Range, rAbove As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Set r = ActiveCell
ActiveCell = Range("B:B").End(xlUp).Offset(2, 0)
Set rAbove = ActiveSheet.Range("B2:" & ActiveSheet.Range("b65536"). _
End(xlUp).Offset(2,0).Address).Select
r.Value = wf.CountBlank(rAbove) / 2
End Sub
Any help would be greatly appreciated!
Last edited: