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, 2015 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. May 28, 2015 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. May 28, 2015 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, 2015 at 11:42

3 Answers 3


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)


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 May 28, 2015 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. May 28, 2015 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, 2015 at 12:06

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()
cellcount = Selection.Cells.count
End Function

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


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


        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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .