Solved

Excel Find Next & Count Rows

Posted on 2004-10-11
12
315 Views
Last Modified: 2010-05-02
Hi - need som help,  I'm trying to calculate the number of cells that are blank between two fields.  

The code below does the following:
  1.  strLBLSearch = General
  2.  lngFirst = 1

Trying to figure our how to get A5 cell value.

A1 = General
A2 =
A3 =
A4 =
A5 = Commercial
A6 = Electrical
A7 =
A8 = Business

I know that I can use the following to calculate the number of blank cells within a range:
   strRange = "A" & lngFirst & ":" & "A" & lngSecond
   intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))

'------------------------------------------------------------------
Dim lngFirst, lngSecond As Long

    For r = 1 To strCells
        x = ws.Cells(r, 1)
        y = ws.Cells(r, 2)
        w = ws.Cells(r, 3)
   
        If strLBLSearch = x Then
            lngFirst = r
        End If
       
    Next r
'--------------------------------------------------------------
0
Comment
Question by:eciabattari
  • 6
  • 3
  • 3
12 Comments
 
LVL 9

Expert Comment

by:Shahid Thaika
Comment Utility
Hi eciabattari. Looks like you are working on a big Excel project. Do you want to look for blank cells row wise or column wise.
0
 

Author Comment

by:eciabattari
Comment Utility
Yes - I'm working on a big Excel project.  So far, most of the times I can figure things out; however, sometimes I just draw a complete blank.

I'm trying to count by row.

Thanks for all the help.
0
 

Author Comment

by:eciabattari
Comment Utility
I really want to know what the next row value is.

Example:
A1 = General         <---------------------- returns row value 1
A2 =
A3 =
A4 =
A5 = Commercial  <------------------------- I want to get this value, should be 5
A6 = Electrical
A7 =
A8 = Business

0
 
LVL 9

Expert Comment

by:Shahid Thaika
Comment Utility
Give me some time. working on it :)
0
 

Author Comment

by:eciabattari
Comment Utility
Figured out a way to do it but it probably isn't the correct way.  Any suggestions on improving would be great.  Thanks


'----------------------------------------------------------------
    'loop that gets data
    For r = 1 To strCells
        p = strLBLRowValue + 1
        x = ws.Cells(r, 1)
        y = ws.Cells(r, 2)
        w = ws.Cells(p, 3)
   
        If strLBLSearch = x Then
            lngFirst = r
            GoTo nextLoop
        End If
    Next r

nextLoop:
   
        For r = lngFirst + 1 To strCells
            p = strLBLRowValue + 1
            x = ws.Cells(r, 1)
            y = ws.Cells(r, 2)
            w = ws.Cells(p, 3)
           
                Do While x <> ""
                    lngSecond = r
                    GoTo nextLoop2
                Loop
               
        Next r

nextLoop2:
    Debug.Print lngFirst & " " & lngSecond

    strRange = "A" & lngFirst & ":" & "A" & lngSecond
   
    Debug.Print strRange
                   
    For i = 1 To strCells
        intNumberMissing = WorksheetFunction.CountBlank(Range(strRange))
    Next
                   
    Debug.Print intNumberMissing
'----------------------------------------------------------------
       
0
 
LVL 9

Expert Comment

by:Shahid Thaika
Comment Utility
I was offline for sometime. Haven't gone through your solution, but here is my sample code. I think it works fine. You need two text boxes... text1 & text2 and a command button command1.



Dim x As New Excel.Application
Dim ew As Workbook
Dim ewk As Worksheet
Dim r As Integer
Dim c As Integer


Private Sub Command1_Click()
Dim intNumberMissing As Integer
Dim n As Integer

n = 1

For c = Text2 To ewk.Cells.SpecialCells(xlCellTypeLastCell).Column
    'to check if first row is empty
    intNumberMissing = WorksheetFunction.CountBlank(Range(ColumnLetter(Text2) & Text1 & ":" & ColumnLetter(Text2) & Text1 + 1))
    If intNumberMissing > 1 Then
        Debug.Assert False
        MsgBox "There are " & intNumberMissing & " cells missing starting from " & ColumnLetter(Text2) & Text1
        Text1 = intNumberMissing + 1
    End If
   
    For r = Text1 To ewk.Cells.SpecialCells(xlCellTypeLastCell).Row - 1
        intNumberMissing = WorksheetFunction.CountBlank(Range(ColumnLetter(c) & Text1 & ":" & ColumnLetter(c) & (r + n)))
        'If all  cells in between two cells are blank then the difference will equal the number of blank cells
        'but if the difference is greater than the blank cell count, it means one of the cells is not blank
        'since we are incrementing by one, the latest cell is not blank.
       
        If ((r + n) - Text1) > intNumberMissing Then
            MsgBox "There are " & intNumberMissing & " cells missing from " & ColumnLetter(Text2) & Text1 & " to " & ColumnLetter(Text2) & (Text1 + intNumberMissing + 1)
            Text1 = (r + n)
            r = Text1 - 1
            n = 0
        End If
        n = n + 1
    Next r
    Text2 = c + 1
    Text1 = 1
Next c
End Sub

Private Sub Form_Load()
Set ew = x.Workbooks.Open("C:\Program Files\Microsoft Office\Office\Library\COMMON.XLS")
Set ewk = ew.Worksheets("Employee Info")
End Sub


Private Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function


Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
ew.Close

Set ew = Nothing
Set ewk = Nothing

x.Quit
Set x = Nothing
End Sub
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:Shahid Thaika
Comment Utility
It took some time to figure out the mathematics and other problems, but finally cracked it :). What I have done is taken one cell, column wise. I loop downwards (by row) and check if the next cell is blank. If it is not, I display the accumulated blank count.

A1 - something
A2 - Blank
A3 - something

Here's how I did it.

1. Take A1 as the initial cell.
2. see the blank cells in the range A1:A2
3. It will be 1.
4. See the value for 2-1 = 1 (How many rows in range)
5. Compare with the value of blank = 1
6. Since it is the same... continue.
7. Do the same for the next (A3)
8. But only 1 blank between 3 & 1, but difference of 2 between 3 & 1.
    Hence, one of the cells between A1 - A3 is not blank. And since, we are checking one by one, it is obvious that the latest cell checked is not blank. Hence we can conclude the solution. :)


Hope this answers your solution. If you have any doubt, I'll answer them tomorrow. It's 03:35 AM out here :P. Gotta sleep :)
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
Comment Utility
As the next non blank cell in Excel can be returned by Ctrl-Page Down you can use the VBA equivalent, ie

Sub CountBlanks()
Dim FirstCell As Range, NextNonBlank As Range
Set FirstCell = Range("A1")
Set NextNonBlank = FirstCell.End(xlDown)
MsgBox "There are " & NextNonBlank.Row - FirstCell.Row - 1 & " blanks"
End Sub

Cheers

Dave
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
And to return row 5 as the next non blank in Column A

Sub CountBlanks()
Dim FirstCell As Range, NextNonBlank As Range
Set FirstCell = Range("A1")
Set NextNonBlank = FirstCell.End(xlDown)
MsgBox "There are " & NextNonBlank.Row - FirstCell.Row - 1 & " blanks" & vbNewLine _
& "Next non-blank cell is in row " & NextNonBlank.Row
End Sub

Cheers

Dave
0
 
LVL 9

Expert Comment

by:Shahid Thaika
Comment Utility
Oooh! Your code is so small Dave. Nice :)
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
Thanks :)

Why don't we see more of you in the Excel TA?
0
 
LVL 9

Expert Comment

by:Shahid Thaika
Comment Utility
I am new in here. Haven't set foot yet on all EE teritories :).
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now