WorksheetFunction.CountA

[Thompyt]

I am trying to get the count of non-empty cells from another sheet in the same workbook. If you use the normal in spreadsheet way it is:
=CountA('WorksheetName'!StartCell:EndCell)


I am trying to use the following to get the count in VBA



ActiveCell = WorksheetFunction.CountA("'" & WkSht.Name & "'!E6:E260") within a For each Wksht in Worksheets - Next loop to get the worksheet name.

I used Debug.Print "'" & WkSht.Name & "'!E6:E260" and the output appears the same as the normal 'WorksheetName'!StartCell:EndCell throughout the sheets in the workbook. The value I get from it = 1 for each sheet. I know this is incorrect.

Sub ListSheetName()Dim WkBk As Workbook, WkSht As Worksheet, NonEmpty As Long


'Tells code which Excel workbook to use
Set WkBk = ThisWorkbook
Set WkSht = WkBk.Worksheets("System Count")


'"Opens" the selected Excel workbook
    WkBk.Activate
    WkSht.Activate


'Adds Header Titles to Systems Count Sheet Columns A & B
        With WkSht
            .Range("A1:B1").EntireColumn.Delete
            .Range("A1:B1").Rows(1).Font.Bold = True
            .Range("A:B").Rows(1).Font.ColorIndex = 1
            .Range("A:B").Rows(1).Interior.ColorIndex = 43
            .Cells(1, 1) = "Network"
            .Cells(1, 2) = "Total Sys Live"
            .Range("A:B").Rows(35).Font.Bold = True
            .Cells(35, 1) = "Total Systems:"
        ActiveSheet.Cells(2, 1).Select
        End With
        
'Get the amount of sheets in the workbook
 RCnt = WkBk.Worksheets.Count
 
'Generate list of hyperlinks to each sheet in workbook in Systems Count Sheet Columns A & B
    For Each WkSht In Worksheets
        Select Case WkSht.Name
            Case "System Count"
            Case Else
                ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & WkSht.Name & "'" & "!A1", TextToDisplay:=WkSht.Name
                    ActiveCell.Offset(0, 1).Select 'Shift 1 Cell right
                        ActiveCell = WorksheetFunction.CountA("'" & WkSht.Name & "'!E6:E260")
                            ActiveCell.Offset(1, -1).Select
       End Select
    Next
    
' Sums the contents of Column B
        ActiveSheet.Cells(RCnt + 1, 2) = WorksheetFunction.Sum(Range("B2:B" & RCnt))
 End Sub

[Micron]

I believe your syntax is only counting a single cell but I can't prove it. I didn't quite understand what I was reading. This form does seem to work though
Application.WorksheetFunction.CountA(Sheets("Sheet 5").Range("A2:O2"))
EDIT - I think the E6:E260 syntax is only looking at E6
With what you have, remove what's in the first cell being referenced and you should get 0

[Thompyt]

Micron,
Thanks This worked the charm ActiveCell = WorksheetFunction.CountA(Sheets(WkSht.Name).Range( "E6:E260"))

[Micron]

You're welcome. You could mark this one a solved then (see thread tools near the top of the page)?