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

Nibbles

Board Regular
Joined
Apr 1, 2002
Messages
75
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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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
 
Upvote 0
When you say "populated", you just mean a non blank cell. You can do that a lot easier with the following:
Code:
MsgBox ActiveSheet.UsedRange.Cells.Count
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count
Is this what you want?
 
Upvote 0
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:
Code:
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
 
Upvote 0
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)
 
Upvote 0
Cells count and paste

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

:cool:

heres the original code
MsgBox ActiveSheet.UsedRange.Cells.Count
MsgBox ActiveSheet.UsedRange.Rows.Count
MsgBox ActiveSheet.UsedRange.Columns.Count
 
Upvote 0
Re: Cells count and paste

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

:cool:

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 ...
Code:
    [a1] = ActiveSheet.UsedRange.Cells.Count
    [a2] = ActiveSheet.UsedRange.Rows.Count
    [a3] = ActiveSheet.UsedRange.Columns.Count

I hope this helps!
 
Upvote 0
Re: Cells count and paste

Hi guys! My question was asked earlier in this forum, but I didn't get much the answer:

I want to use the number of populated cells as part of my sub procedure.

It looks like this

Code:
Sub extractdata()

Dim i As Integer
Dim j As Integer


For i = 1 To Countdata()
    For j = 1 To Countdata()
        If Worksheets("Masterlist").Range("F" & i) = "unsold" _
        Then Range("F" & i).EntireRow.Copy Destination:=Worksheets("Unsold").Range("a" & j)
i = i + 1
j = j + 1
    Next
Next

End Sub

Sub Countdata()
'here is where I need help

I need the count value in order to limit the data that I'm processing, to make the procedures run faster.

Thanks for the help!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top