VBA to count the number of populated cells...?

[Nibbles]

Hi!

I have around two hundred workbooks of identical format, each with 3 worksheets. I need to know how many populated cells there are within these worksheets. For my purposes, I can assume that if a cell in column A is populated, that row will be populated. As I know the number of columns, knowing the number of populated cells in column A will allow me to calculate a total. The populated cells in column A are continuous, i.e. the data will run straight from A2 to say A787 with no gaps.

I have some code already to open up each workbook, so all I need is the bit to add up the number of populated cells in column A of each of the 3 worksheets (they all have a different number of columns and therefore I need the number of populated rows for each sheet). I suppose using .End(xlUp) would provide me with the last populated row - but how do I get this recorded somewhere, for each of the three sheets, for each of the 200 workbooks?

Any help much appreciated!

Nibbs

[Nibbles]

I've worked away at it and managed to come up with a solution myself. I'll show the code below for future use if anybody ever needs to count populated cells! This procedure assumes you have a list of filenames that you want to open in column A. It may be crude but here it is:

Sub Count_Cells()

Dim rCells As Range
Dim strBook As String

Application.ScreenUpdating = False

For Each rCells In ThisWorkbook.Worksheets(1).Range _
("A1", ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp))
strBook = rCells.Value
Application.StatusBar = "Counting Cells in " & strBook
Workbooks.Open strBook

Sheets("Sheet1").Select
FinalRow1 = Range("A15000").End(xlUp).Row
Sheets("Sheet2").Select
FinalRow2 = Range("A15000").End(xlUp).Row
Sheets("Sheet3").Select
FinalRow3 = Range("A15000").End(xlUp).Row
ActiveWorkbook.Close SaveChanges:=False

Workbooks("Count Cells").Activate
Sheets("Cell Totals").Select
Output1 = Range("A15000").End(xlUp).Row
Range("A" & Output1 + 1).Value = FinalRow1 - 1
Output2 = Range("B15000").End(xlUp).Row
Range("B" & Output2 + 1).Value = FinalRow2 - 1
Output3 = Range("C15000").End(xlUp).Row
Range("C" & Output3 + 1).Value = FinalRow3 - 1

Next rCells

Application.StatusBar = False
MsgBox "All Cells Counted"

End Sub

[Al Chara]

When you say "populated", you just mean a non blank cell. You can do that a lot easier with the following:

MsgBox ActiveSheet.UsedRange.Cells.Count
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count

Is this what you want?

[Yogi Anand]

On 2002-04-18 09:18, Al Chara wrote:
When you say "populated", you just mean a non blank cell. You can do that a lot easier with the following:

MsgBox ActiveSheet.UsedRange.Cells.Count
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count

Hi Al:
I put this as a Sub in Personal Workbook, and the sub works in the worksheets of the Personal Workbook ... that's Great!, but it does not run in the worksheets of another workbook ... two questions

1) how can I make this sub run in the worksheets of another workbook
2) what else would be needed to convert this into a udf?

Thanks!

Yogi Anand

[Al Chara]

To make it a UDF in available in other worksheets try the following:

Function USEDINFO(INFO)
'
' USEDINFO Function
' Worksheet Used Info (1=cells, 2=rows, 3=columns)
'
If INFO = 1 Then
USEDINFO = ActiveWorkbook.ActiveSheet.UsedRange.Cells.Count
ElseIf INFO = 2 Then
USEDINFO = ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count
ElseIf INFO = 3 Then
USEDINFO = ActiveWorkbook.ActiveSheet.UsedRange.Columns.Count
End If
End Function

To use it:
=PERSONAL.XLS!USEDINFO(2)

[msb221]

Hey Guys.
This forula is great, but instead of bringing up a message box, how can you paste the numbers into a specified cell?



heres the original code
MsgBox ActiveSheet.UsedRange.Cells.Count
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count

[Yogi Anand]

Originally Posted by msb221

Hey Guys.
This forula is great, but instead of bringing up a message box, how can you paste the numbers into a specified cell?



heres the original code
MsgBox ActiveSheet.UsedRange.Cells.Count
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count

Hi Mark:

Let us say you wanted to paste the results into cells A1, A2, and A3, then ...

 
    [a1] = ActiveSheet.UsedRange.Cells.Count
    [a2] = ActiveSheet.UsedRange.Rows.Count
    [a3] = ActiveSheet.UsedRange.Columns.Count

I hope this helps!