Improve company productivity with a Business Account.Sign Up

x
?
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
Medium Priority
?
120 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
7 Comments
 
LVL 81

Assisted Solution

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

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 53

Accepted Solution

by:
Martin Liss earned 1200 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

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 53

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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

585 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