• Status: Solved
• Priority: Medium
• Security: Public
• Views: 329

# Excel Find Next & Count Rows

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 =

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
eciabattari
• 6
• 3
• 3
1 Solution

Sole ProprietorCommented:
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 Commented:
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 Commented:
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 =

0

Sole ProprietorCommented:
Give me some time. working on it :)
0

Author Commented:
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

Sole ProprietorCommented:
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

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

ew.Close

Set ew = Nothing
Set ewk = Nothing

x.Quit
Set x = Nothing
End Sub
0

Sole ProprietorCommented:
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

Commented:
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

Commented:
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

Sole ProprietorCommented:
Oooh! Your code is so small Dave. Nice :)
0

Commented:
Thanks :)

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

Sole ProprietorCommented:
I am new in here. Haven't set foot yet on all EE teritories :).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.