Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 117
  • Last Modified:

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

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
EdLB
Asked:
EdLB
  • 3
  • 3
2 Solutions
 
byundtCommented:
MyVariable = Application.CountA(Worksheets("Sheet1").Range("A2:A10000"))
0
 
Martin LissRetired ProgrammerCommented:
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
 
Martin LissRetired ProgrammerCommented:
Or a touch more efficiently

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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
EdLBAuthor Commented:
I used MyVariable = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
0
 
EdLBAuthor Commented:
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
 
Martin LissRetired ProgrammerCommented:
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
 
EdLBAuthor Commented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now