Option
Explicit
Public
intRows
As
Integer
Public
Sub
outputLastrow(
ByVal
m
As
String
, columnNo
As
Integer
)
Worksheets(m).Activate
intRows = 9
With
Worksheets(m)
intRows = IIf(Len(Worksheets(m).Cells(Worksheets(m).Rows.Count, 1)), Worksheets(m).Rows.Count, Worksheets(m).Cells(.Rows.Count, 1).
End
(xlUp).Row)
Debug.Print intRows
intRows = Worksheets(m).Columns(columnNo).Find(
"*"
, .Cells(1, columnNo), xlValues, xlWhole, , xlPrevious).Row
Debug.Print intRows
End
With
End
Sub
Public
Sub
nocomp()
Dim
rngRange
As
Range
Call
outputLastrow(
"data"
, 6)
rngRange = Worksheets(
"data"
).Range(Cells(9, 6), Cells(intRows, 6))
MsgBox Evaluate(
"=SUM(IF("
& rngRange &
"="
""
",0,1/COUNTIF("
& rngRange &
","
& rngRange &
")))"
)
End
Sub