Counting Non-Blank Cells Under a Given Heading

Question

I have a large list of similar reports, and these reports each have a "Name:" cell in the first column and a set of objects listed, say, "Objects" in the same column. After two instances of the string "Name:" and some other rows in between (both blank and filled) we arrive at the "Objects" string. After each instance of the word "Objects" there is a list of numbers counting off the total number of objects. There are an arbitrary number of objects in each list, but the fact that they always come after the title "Objects" is constant. Is there an easy way to have Excel search through each report and place the name found under "Name:" in some cell and the count of all non-blank cells after the word "Objects" beside it? Since the list of objects is unbroken the first blank cell could trigger the count to stop and move onto the next list while a large number of blank cells (say more than 300) could trigger the program to declare that there are no more entries to be pulled out and stop).

Example

    A              B           C

    Name:      John Smith


    Date:       March 5th

    Name:       John Smith

   Objects
    List 
      1            Filler1     Something1
      2            Filler2     Something2
      3            Filler3     Something3
      4            Filler4     Something4
      5            Filler5     Something5
      6            Filler6     Something6

    Name:      Jane Doe


    Date:       March 8th

    Name:       Jane Doe

   Objects
    List 
      1            Filler1     Something1
      2            Filler2     Something2
      3            Filler3     Something3
      4            Filler4     Something4

The result would then be something like:

    John Smith         6
    Jane Doe           4

Is it possible to kindly provide a screenshot and/or a sample data as well as expected results? That way it's easier to convey what you want. — WGS, Mar 27 '14 at 21:11
okay, so I now see what the (data) situation looks like. Can you also show how the result is supposed to look like? It's not really clear from your question... — Peter Albert, Mar 27 '14 at 21:51
`but the fact that they always come after the title "Objects" is constant.` I guess you meant `Object` and not `Objects`? Yes, you can easily achieve what you want by using `.Find` to find the cell which has that word and then simply get the relevant number. [THIS](http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/) will get you started. — Siddharth Rout, Mar 28 '14 at 03:40
@Siddharth Rout Thanks, I'll definitely take a look at that! — 114, Mar 28 '14 at 14:58
@siddharth I think one problem that I still need to tackle with this is how to have Excel count the non-blank cells below the found cell and continue until it finds a blank cell. Are there similar tutorials for this? — 114, Mar 28 '14 at 15:34
Also look at the CountA function, combined with the results of find, I think will get you where you are trying to get. — Alan Waage, Mar 28 '14 at 16:28
It's pretty easy actually. After you find the cell which has `Object`, find the last row in that column using [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) and then use `CountA` as @alanWaage suggested `Application.WorksheetFunction.CountA()` to find non blank cells. — Siddharth Rout, Mar 30 '14 at 05:03
I have a solution - once you clarify a couple items: (1) Can there be multiple groups of objects beneath the name, each followed by one or more blank lines? and (2) Are the reports to be parsed in different workbooks, or just on different sheets? (3) Your example shows a blank row after the 'Object'.. is that always the case or can there be no blank row? Currently I can parse multiple objects for one name, but then you wouldn't show the number on the same line as the name (per your example)... — Wayne G. Dunn, Mar 31 '14 at 00:23
@Wayne (1) Yes, that can be possible - it may be that there is a blank space between list items going down the column, for example. (2) Each count for a "Name:" and the corresponding "Objects" list would ideally be stacked into a table in a separate sheet. (3) There will, in the real example, be text filling that blank space in many cases, and this text should not be counted. However, my solution to this was just to subtract 1 from every count. — 114, Mar 31 '14 at 01:44
@Wayne As an additonal note, if the solution could search to the right of each object being counted under "Objects" and place that in the table this would be even better, but I think I could piece that part together. — 114, Mar 31 '14 at 01:45
Since you can have multiple groups of objects beneath a name, and may or may not have blank rows to delimit, how can you determine if the row contains an 'Object' name, or is list-data associated with that object? When you added "text filling the blank space ... not be counted" there has to be a rule. — Wayne G. Dunn, Mar 31 '14 at 04:33
@Wayne You make a good point. I will have to modify for the case where there are groups. Assume for this question that there are no blank spaces between list items. — 114, Mar 31 '14 at 13:10

Wayne G. Dunn · Accepted Answer · 2014-04-03T16:13:45.523

Revised 4/3: Change object name; change CASE; Revised again on 4/2: Search for a specific type of Object; Revised 4/2; Skip first 'Name'; End loop if Max reached (Input format issue!) Revised 4/1; added error trap & line #'s to find error. Would help to see users input. See notes in code.

    Option Explicit
' Assumptions:
' (1) All data in first column - except for name.
' (2) The literal 'Name:' will be in Col A; The name (i.e. 'John Doe') will be in Col B.
' (3) The same 'Name' will appear twice, with a 'Date' row between the two.
' (4) May be blank row(s) anywhere before or after row containing 'Name'.
' (5) 'Object' row will have string starting with 'Objects' in Col A, followed by Object Name (i.e. 'Objects Cars')
' (6) 'Object' row may repeat for ONE name.
' (7) Search for user specified Object in list for a Name. Set to zero if not found
' (8) Name will be repeated many times in the column (>100,000 rows).

' For test purposes, I have used 'Sheet1' as report sheet, and 'Sheet2' as output.
' Can change to process ALL sheets in a workbook (not sure how your reports are found (.. sheets or workbooks..)

Sub Create_Summary()
Dim lLastRow    As Long
Dim lRow        As Long
Dim lOutRow     As Long
Dim lNameRow    As Long

Dim sName       As String
Dim iNameCtr    As Integer
Dim lRowCt      As Long
Dim blnSkip     As Boolean
Dim strObjName  As String
Dim strObjKey  As String
Dim strObjNameFound As String

1000  On Error GoTo Error_Trap

'Get last used row
1010  lLastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
1020  Debug.Print "Total Rows: " & lLastRow

1030  strObjKey = "Objects"     ' <<<<<<< Add code to obtain and set to whatever you want.
1040  strObjName = "Cars"     ' <<<<<<< Add code to obtain and set to whatever you want.

1050  lOutRow = 1
1060  blnSkip = False
1070  For lRow = 1 To lLastRow
1080      iNameCtr = 0
1090      lRowCt = 0
1100     Do Until iNameCtr = 2 Or lRow >= lLastRow    'Trim(Cells(lRow, 1)) = "Name:" Or lRow >= lLastRow      ' Find 'Name'
1110          If Trim(Cells(lRow, 1)) = "Name:" Then
1120              iNameCtr = iNameCtr + 1
1130              lNameRow = lRow
1140          End If
1150          lRow = lRow + 1
1160       Loop
1170      lRow = lRow - 1
1180      If lRow >= lLastRow - 1 Then Exit For
1190      If blnSkip = True Then
1200         sName = Cells(lRow, 2)            ' Name is in Col 2
1210         Debug.Print "Row: " & lRow & vbTab & ">> Name: " & sName
1220         Sheets("Sheet2").Range("A" & lOutRow) = sName      ' Save Name
            ' There will always be a non-blank row after 'Name' do not count that!
1230         lRow = lRow + 1
1240         Do Until LCase(Left(Cells(lRow, 1), 7)) = LCase(strObjKey) And InStr(8, LCase(Cells(lRow, 1)), LCase(strObjName)) > 0    ' Find 'Object'
1250             lRow = lRow + 1
1260             If LCase(Trim(Cells(lRow, 1))) = LCase("Name:") Then     ' Means never found desired 'Objects'
1270                 Sheets("Sheet2").Range("B" & lOutRow) = 0
1280                 lRow = lRow - 1
1290                 lOutRow = lOutRow + 1
1295                 GoTo Next_Row
1300             ElseIf lRow > lLastRow Then
1310                 Sheets("Sheet2").Range("B" & lOutRow) = lRowCt
1320                 Debug.Print "**** Exit because at end of used range!"
'134                 MsgBox "Found name: '" & sName & "' at row " & lNameRow & ", but there was no matching 'Objects'", vbOKOnly, "Sheet Format Incorrect"
1330                 Exit For
1340             End If
1350         Loop
1360         Debug.Print "Row: " & lRow & vbTab & ">> " & strObjKey & ": " & Cells(lRow, 1)
1370         strObjNameFound = Trim(Mid(Cells(lRow, 1), 8, 99))
1380         lRow = lRow + 2   ' Must skip a 'filler' line after 'Objects'

1390         Do Until Cells(lRow, 1) = "" Or LCase(Left(Cells(lRow, 1), 7)) = LCase(strObjKey) Or lRow >= lLastRow      ' Find Blank line
1400             If Cells(lRow, 1) <> "" Then
1410                 lRowCt = lRowCt + 1   ' Count Rows associated with Object
1420             End If
1430             lRow = lRow + 1
1440         Loop
1450         Debug.Print "Row: " & lRow & vbTab & "# " & strObjKey & ": " & lRowCt
1460         Sheets("Sheet2").Range("B" & lOutRow) = lRowCt
1470         Sheets("Sheet2").Range("C" & lOutRow) = strObjNameFound
1480         lOutRow = lOutRow + 1
1490     Else
1500         blnSkip = True
1510         lRow = lRow + 1
1520     End If
Next_Row:
1530  Next lRow

1540  Exit Sub

Error_Trap:
    Debug.Print Err.Number & vbTab & Err.Description & vbCrLf & _
            "At Line: " & Erl & vbCrLf & _
            "lLastRow = " & lLastRow & vbTab & "lRow = " & lRow
    MsgBox "Error: " & Err.Number & vbTab & Err.Description & vbCrLf & "At Line: " & Erl & vbCrLf & _
            "lLastRow = " & lLastRow & vbTab & "lRow = " & lRow
    Exit Sub
End Sub

@Wayne All that sounds good. Thank you for your patience! One modification would be to allow the program to choose between "Objects Cars" or "Objects Books" by searching for one or the other using .Find. That is, by changing what is being searched for under the second name one could get the count for either one of "Objects Cars" or "Objects Books" — 114, Apr 02 '14 at 23:12
(1) For that revision, I need you to update the question to show the format and content of the 'Objects' cell(s). (2) What if the searched for object name (i.e. Cars) does not exist? (3) Will there ever be more than two objects for a persons name? — Wayne G. Dunn, Apr 02 '14 at 23:44
@Wayne (1) It would be the same as above but the "Objects" header could be named "Objects Cars" or "Objects Books", left-aligned and with no white-space. (2) Well, they wouldn't be searching for the object name but for either the heading "Object Cars" or "Object Books". If the heading didn't exist then an error could be returned. (3) There should only be 2, but keep in mind that there is only one of each (Cars, Books), and they would never be counted together as they are separate categories. I think part of the confusion here may be coming from my choice of the word 'object'. — 114, Apr 03 '14 at 00:14
Stack Overflow doesn't work so well for live debugging, I suggest you take it to a chat room if you want live debugging or consulting. — George Stocker, Apr 03 '14 at 00:37
@George Stocker - Good Idea! Stopwatch, I posted what I hope is final change - you need to specify on line 35 what type of Object to search for. If further issues, let's chat. — Wayne G. Dunn, Apr 03 '14 at 00:42
@Wayne Thank you for all of your help! Maybe we could chat further briefly? After changing the "Objects" header name to the real header name in a number of places the code works perfectly up to the 750th name at which point it gives a count of 0 again (this is around the 150,000th row). I double checked and the formatting for the reports counted successfully and the ones counted unsuccessfully is the same, so I am wondering if there might be an implicit row limit or something of the sort. — 114, Apr 03 '14 at 15:23

Counting Non-Blank Cells Under a Given Heading

1 Answers1