asked on

VBScript macro to count fields in Excel worksheet?

Excel 2003

I know this can be accoplished using Excel's built in functions, but I need it to run from a macro.

I have a spreadsheet and want to count the number of names in column A.
Column A has misc headings from row 1 to row 8.
Data always starts on row 9 and continues for a different number of rows depending on report content.
There is a blank line after the last row of data followed by the phrase "Created on 6/1/06" --( the date changes depending on when the report is run.)
The results should be displayed in cell X1.

How can I count the number of names in the column?

ASKER CERTIFIED SOLUTION

mvidas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

dave4dl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

prafful_nagwani

Hi summitMiS

This should solve your problem.

Dim totalRows As Integer
totalRows = ActiveSheet.UsedRange.Rows.Count
totalRows = totalRows - 8 'Removing first eight header rows
totalRows = totalRows - 2 'Removing last two rows (Blank and Created By)
MsgBox totalRows

Regards,
Prafful

prafful_nagwani

Hi summitMiS

This should solve your problem. Sorry forgot the X1 Cell :)

Dim totalRows As Integer
totalRows = ActiveSheet.UsedRange.Rows.Count
totalRows = totalRows - 8 'Removing first eight header rows
totalRows = totalRows - 2 'Removing last two rows (Blank and Created By)
Range("x1").value = totalRows

Regards,
Prafful

prafful_nagwani

Hi,

Any reasons, why the points are not shared with me???

I am new user, so apologies, if I am being rude with my question.

Regards,
-Prafful Nagwani