?
Solved

VBScript macro to count fields in Excel worksheet?

Posted on 2006-06-01
7
Medium Priority
?
2,006 Views
Last Modified: 2009-12-16
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?
0
Comment
Question by:summitMIS
  • 3
5 Comments
 
LVL 35

Accepted Solution

by:
mvidas earned 1000 total points
ID: 16810906
Hi summitMIS,

Give the following a try, should be what you're looking for!

 Range("x1").Value = IIf(Cells(Rows.Count, 1).End(xlUp).Row > 10, _
  Cells(Rows.Count, 1).End(xlUp).Row - 10, 0)

Matt
0
 
LVL 15

Assisted Solution

by:dave4dl
dave4dl earned 1000 total points
ID: 16814107
try this:
sub macro1()
     'written this way because i assume you have other things you need this macro to do
     Range("x1").value = NumberOfNames()
end sub
private function NumberOfNames() as double
     dim curCell as Range
     dim valueCount as double

     set curCell = range("A9")
     
     do until curCell.value = ""
          valueCount = valueCount+1
          set curCell = curCell.offset(1)
     loop
     NumberOfNames = valueCount
end function

This function could be modified to keep a list of the values you had run across already if you meant you wanted a list of distinct names/values in column A.
0
 
LVL 3

Expert Comment

by:prafful_nagwani
ID: 16982603
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
0
 
LVL 3

Expert Comment

by:prafful_nagwani
ID: 16982608
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
0
 
LVL 3

Expert Comment

by:prafful_nagwani
ID: 17230294
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

864 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