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

  • Is point 2 saying you know you want to select A3:A6 and B:5 to B:11 and C1:C22 and you want to have a quickway to select them? – Dave May 28 '15 at 9:50
  • #2 could also be done with VBA but it is complex as you would need a way of accumulating the answer between cell selections. Possibly a button to start and another to reset. It would take a lot of working out. My advise would be to create a second question stating what you wanted to ACHIEVE so that you get some alternative approaches. – Julian Knight May 28 '15 at 10:09
  • No, @Dave, I know I want to select, for example, 40 cells, and then I want to make selections in between existing appointments (like the image), until 40 cells are selected. – Mark Jeronimus May 28 '15 at 11:40
  • What is wrong with holding down CTRL and selecting the cells you want? Or do I still not understand the question :S – Dave May 28 '15 at 11:42
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
  • I changed the messagebox to Application.StatusBar = totalCells and that solved my point #2 – Mark Jeronimus May 28 '15 at 12:01
  • Now if I could only make this script global so I can use it in all sheets of all workbooks, it would be awesome. – Mark Jeronimus May 28 '15 at 12:03
  • You could hack it and copy it to each, but your quesiton is good enough to ask a new quesiton! – Dave May 28 '15 at 12:06
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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.