View Full Version : Count of cells using VBA
karira
06-13-2013, 10:32 AM
Dear friends,
I did a search on this topic within the forum but was not able to find anything that addressed my specific query.
Background</SPAN> - I have a spreadsheet with 60 tabs, multiple cells with formulas, multiple cells that act as inputs (values, text).
Question</SPAN> - I need to figure the following for the entire workbook:
1. Count of cells being used within the entire workbook
2. Count of cells that have formulas in them
3. Count of Cells that act as inputs (values/text) and have formulas dependent on them
4. Count of Cells that act as inputs (values/text) and but have NO formulas dependent on them
Can someone suggest some kind of a macro to address this.
Any help is much appreciated.
Cheers, gk</SPAN>
GarysStudent
06-13-2013, 12:11 PM
Here are the first two parts:
For USED:
Sub UseCounter()
Dim s As Worksheet, r As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Dim Kount As Long
Kount = 0
For Each s In Sheets
s.Select
Kount = Kount + wf.CountA(ActiveSheet.UsedRange)
Next s
MsgBox Kount
End Sub
For FORMULAS:
Sub FunctionCounter()
Dim s As Worksheet, r As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Dim Kount As Long
Kount = 0
For Each s In Sheets
s.Select
For Each r In ActiveSheet.UsedRange
If r.HasFormula Then
Kount = Kount + 1
End If
Next r
Next s
MsgBox Kount
End Sub
karira
06-13-2013, 02:14 PM
Hi GarysStudent, thank you so much for taking the time out to look at my request. I will just test this out and post the results.
Any luck with bullet # 3 and 4?
Best, gk
GarysStudent
06-13-2013, 02:18 PM
Hi gk:
I don't know how to approach items 3 & 4
mancubus
06-13-2013, 03:25 PM
i dont know either.
but post 18 here may give you an idea:
http://www.xtremevbtalk.com/showthread.php?p=574302
ps: membership is required to download attachments.
p45cal
06-13-2013, 05:31 PM
I don't know how to approach items 3 & 4 GarysStudent, It's past my bedtime now but you may be able to dream up something along the lines of your HasFormula solution using range.dependents which returns a range and if that range is nothing then that cell has no dependents.
There are also DirectPrecedents, DirectDependents, Dependents and Precedents properties to check out.
GarysStudent
06-13-2013, 05:38 PM
Thanks!
Sub M_snb()
for each sh in sheets
c00=c00 +sh.usedrange.cells.count
c01=c01+sh.usedrange.specialcells(-4123)
for each it in sh.usedrange.specialcells(-4123)
for each it2 in it.precedents
if instr(c03 & "|","|" & it2.address & "|")=0 then c03= c03 & "|" & it2.address
next
next
next
msgbox "number of usedcells " & c00 &vblf & "number of formulacells " & c01 & vblf & "number of prcedents " & ubound(split(c03,"|"))
End Sub
karira
06-14-2013, 01:03 PM
Hi snb,
Thanks for your response. The code does work but not all files. On some files I get error with the following statement
c01=c01+sh.usedrange.specialcells(-4123)
Based on my reserach, it seems like usedrange can lead to some errors at times.
Any help will be greatly appreciated.
No it has nothing to do with usedrange. Dive into the VBEditor's helpfiles; lemma specialcells.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.