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
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.
Question by:EdLB
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 81

Assisted Solution

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

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))
LVL 47

Accepted Solution

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

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

Author Comment

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.
LVL 47

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.

Author Comment

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.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article will show, step by step, how to integrate R code into a R Sweave document
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

730 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