# Search and Count in a Excel sheet

Asked By 220 points N/A Posted on -

Hi,

I have an Excel sheet containing about 1,200 rows. Each of the rows has different customer information. I want to know how many of them have dues.

Problem is the number of rows is not fixed. They may vary. So I can’t count the number of empty cells and just minus it from total rows. Do you have any suggestion?

SHARE
Best Answer by Simmy C-me
Answered By 0 points N/A #97906

## Search and Count in a Excel sheet

You don’t need to specify the limit in the loop. You can check for a column that is compulsory in the rows (always having value) as base and check whether it is blank.

If the specific column is blank then you can break it. Here is an example:

Do
If Sheet3.Cells(r, 1) = "" Then                               ' assuming that the 1st column is always not null
Exit Do
Else

' do your job here
End If
r = r + 1
Loop

Answered By 5 points N/A #97907

## Search and Count in a Excel sheet

If there is a serial number in the sheet. Then you can use it to control the loop.

Answered By 220 points N/A #97908

## Search and Count in a Excel sheet

Thanks but  the serial number in my table is like EF22310.  And I do not have any cell to track the total number of customer either.

The 1st row ID is compulsory. And the dues filed is in the 4th column. So what will it be?

Best Answer
Best Answer
Answered By 0 points N/A #97909

## Search and Count in a Excel sheet

Create an ActiveX button in the sheet. I am guessing your data is in sheet3.

In developer mode double click the button and paste this code

Dim r As Integer
Dim dues As Integer
r = 1
dues = 0
Do
If Sheet3.Cells(r, 1) = "" Then
Exit Do
Else
If Sheet3.Cells(r, 3) <> "" Then                                   ' substitute 3 with your column of dues.
dues = dues + 1
End If
End If
r = r + 1
Loop
MsgBox (dues)

Answered By 5 points N/A #97910

## Search and Count in a Excel sheet

If your dues column is of Number type. Then most likely you would use

If Sheet3.Cells(r, 2) <> "0" Then

instead of just

If Sheet3.Cells(r, 2) <> "" Then

Answered By 220 points N/A #97911

## Search and Count in a Excel sheet

Thank you Simmy. Thanks goes to you too Angel.  You guys have been of real help.

Answered By 0 points N/A #97912

## Search and Count in a Excel sheet

Welcome. Glad to know it helped.