VBA to count the number of populated cells...?
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

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

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Surrey, United Kingdom
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    New Member
    Join Date
    Aug 2005
    Location
    Tonbridge
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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?



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

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cells count and paste

    Quote 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 ...
    Code:
     
        [a1] = ActiveSheet.UsedRange.Cells.Count
        [a2] = ActiveSheet.UsedRange.Rows.Count
        [a3] = ActiveSheet.UsedRange.Columns.Count
    I hope this helps!
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  8. #8
    New Member
    Join Date
    May 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com