Sep 23 2021 07:10 AM
Hello everyone. I am very new to Excel Macros and am trying to create a Macro that returns the number of rows in a given sheet where the value of column A is <>0.
I have been able to create a command button but don't know how to proceed to include the WorksheetFunction.CountIf section of the code.
Sheet name = Test
Column A
Click button
Return number of rows on Test where value of column A is <>0.
Sep 23 2021 07:16 AM - edited Sep 23 2021 07:24 AM
Solution
YourVariable=Application.WorksheetFunction.CountIfs(Range(YourRange), "<>0")
Sub CountValuesOnColumnA()
Dim YourOutput As Long, YourRange As Range
With ActiveSheet.Range("A1").CurrentRegion
Set YourRange = .Offset(1).Resize(.Rows.Count - 1, 1)
YourOutput = Application.WorksheetFunction.CountIfs(YourRange, "<>0")
End With
MsgBox YourOutput
End Sub
Sep 23 2021 07:22 AM
Sep 23 2021 07:33 AM - edited Sep 23 2021 07:37 AM
To be honest, you dont need VBA for that.
You just need an ordinary formula like =COUNTIF(OFFSET($A$2,0,0,COUNTA(A:A)-1,1),"<>0")
Then you click over your retangle and type the address where the result of the formula is.
If you really need VBA (Not recommended)
Sub CountValuesOnColumnA()
Dim YourOutput As Long, YourRange As Range
With ActiveSheet.Range("A1").CurrentRegion
Set YourRange = .Offset(1).Resize(.Rows.Count - 1, 1)
YourOutput = Application.WorksheetFunction.CountIfs(YourRange, "<>0")
End With
ActiveSheet.Shapes(YourRetangleName).TextFrame.Characters.Text = YourOutput
End Sub
Sep 23 2021 07:16 AM - edited Sep 23 2021 07:24 AM
Solution
YourVariable=Application.WorksheetFunction.CountIfs(Range(YourRange), "<>0")
Sub CountValuesOnColumnA()
Dim YourOutput As Long, YourRange As Range
With ActiveSheet.Range("A1").CurrentRegion
Set YourRange = .Offset(1).Resize(.Rows.Count - 1, 1)
YourOutput = Application.WorksheetFunction.CountIfs(YourRange, "<>0")
End With
MsgBox YourOutput
End Sub