3

How do I count the number of selected cells, regardless of cell content?.

There are two use cases. I need use case 2 right now:

  1. When I make one or multiple selections, I want to know how many cells,
  2. I want to select exactly N cells using multiple selections. I keep making and resizing selections until the number (which should be live on the screen somewhere) reaches N

For #1 I have a workaround: Select single cell containing text, copy (so it has an animated border), then make your selections, press paste, and read the count in the status bar.

For #2 I don't know a workaround that doesn't involve repeating workaround #1 dozens of times and then pressing undo.

enter image description here

Mark Jeronimus
  • 761

3 Answers3

1

To address point #1, you can use this VBa. The problem is you don't explain how you want to see this detail!? So I've added it as a Message Box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim totalCells As Long
totalCells = Selection.Cells.Count

MsgBox (totalCells)
End Sub

I added the VBa to Sheet1. Also see How do I add VBA in MS Office?

Just don't select all cells as it will through an overflow exception

enter image description here

The problem is the pop up will occur every time, I'd suggest you replace

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

MsgBox (totalCells)

with

Range("M1").Value = totalCells ' CHANGE THE RANGE TO THE CELL YOU WANT
Community
  • 1
Dave
  • 25,513
0

Thanks so much, everyone. Your above info may be a few years old, but is exactly what I needed today. In case it generates ideas for someone else, here's how I tweaked the above for my needs. (Our cost isn't actually $2/hour, of course. But you get the idea.) Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Const HOURS As Integer = 10        ' hours per cell
    Const COST As Integer = 2 * HOURS  ' dollars per cell
    Const SPACER As String = "   "

    Dim numCells As Long

    numCells = Selection.Cells.Count

    If numCells < 2 Then

        Application.StatusBar = False

    Else

        On Error Resume Next ' in case the selection is so large it causes an overflow error
        Application.StatusBar = "Cells: " & numCells _
                     & SPACER & "Hours: " & (numCells * HOURS) _
                     & SPACER & "Cost: " & FormatCurrency(numCells * COST, 0)

    End If

End Sub
Aboat
  • 1
0

The only way I could think to do what you want would be with VBA.

If you wanted a function to put this in a cell - something like this would work:

Public Function cellcount()
Application.Volatile
cellcount = Selection.Cells.count
End Function

You can re-run the function by pressing F9 to get the answer after each selection change.

Trum
  • 101