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
110 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

803 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