Solved

Using EXCEL VBA, I want to determine the number of non-blank rows in a column and put that value into a variable.

Posted on 2015-01-26
8
111 Views
Last Modified: 2015-01-27
I have a list of parts in a single column in a spreadsheet. I would like to count the number of parts in the column and put that value into a variable.
0
Comment
Question by:EdLB
  • 3
  • 3
8 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 200 total points
ID: 40571053
MyVariable = Application.CountA(Worksheets("Sheet1").Range("A2:A10000"))
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40571257
MyVariable = Application.CountA(Worksheets("Sheet1").Range("A2:A10000")) would ignore data if there are more than 10000 rows. Do this if that's a possibility.

MyVariable = Application.CountA(Worksheets("Sheet1").Range("A2:A1" & Worksheets("Sheet1").UsedRange.Rows.Count))
0
 
LVL 46

Accepted Solution

by:
Martin Liss earned 300 total points
ID: 40571259
Or a touch more efficiently

With Worksheets("Sheet1")
    MyVariable = Application.CountA(.Range("A2:A1" & .UsedRange.Rows.Count))
End With

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:EdLB
ID: 40571436
I used MyVariable = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
0
 

Author Comment

by:EdLB
ID: 40571464
I realize that's not the same thing exactly that I asked for but it suffices for what I am trying to do. If there are non-blank rows in the list, the row number provides the count of items in the list. That is a precondition for the list, which I failed to mention in my question.

Thanks for the help.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40571515
I'm Requesting Attention for this thread because while I could have come up with byundt's code on my own, I did use it in my solution so I propose that the points be split between us.

@EdLB: If you can explain what you mean when you say "the row number provides the count of items in the list", perhaps I or byundt can give you what you really want.
0
 

Author Comment

by:EdLB
ID: 40571609
Since there are no blanks rows between the first row in the column and the last row of the column, the count of the rows equals the count of items (minus 1 header row) Splitting points works for me. Not sure how to split the points since they've been designated already.

However Martin, after reviewing your and code, I went with it because it provides the number of non-blank entries in the column even if there are blank rows.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

808 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