[Webinar] Streamline your web hosting managementRegister Today

x
?

Get specific column row count within a selected range.

Posted on 2018-02-13
9
Medium Priority
?
17 Views
Last Modified: 2018-02-15
I found the following online and would like to know how to read and edit this.  What each of the 1's are?  I do already understand that the -1 is to not count headers.

MsgBox Cells(1).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

What column of the CurrentRegion.Columns is this?  If I place data in column A it works, but if the data range starts in cell C3, returns zero.

How would I edit this to tell me, what the row count is for a specific columns in a range, no matter where the current selection is?  The first column in the range has x ,  the third column in the range has x.....

This would still be for only visible cells in the preselected range, and not count any blank cells.
0
Comment
Question by:RWayneH
  • 5
  • 4
9 Comments
 
LVL 54

Expert Comment

by:Rgonzo1971
Hi,

 Cells(1)  is A1 if you look for 1st column of  current region adjacent to A1

then try
MsgBox Cells(1).CurrentRegion.Columns(3).SpecialCells(xlCellTypeVisible).Count - 1

Open in new window

REgards
0
 

Author Comment

by:RWayneH
I am still confused on how to read this.  If Cells(1) is A1?  What if my selected range is C3:F10 and each column has have a different amount of visible cells with values.  How do I start this MsgBox using C3?  and I am still not sure what the (3) is for?

So if I start at C3, how would I tell it to retrieve the number of visibles in column E?
0
 
LVL 54

Expert Comment

by:Rgonzo1971
then try
MsgBox Range("C3").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1

Open in new window

Columns(1) in this case takes the first column of the current region where c3 is included
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:RWayneH
Still not working.  If may range is C3:N9  So the first part is Range("C3").   I get that.  So how do I tell it to count the visibles in column E?  

It would be offset two columns to the right of C3?  When I chg .Columns(1) to .Columns(3) I am getting the same result and it should be different.  I guess I am not getting how to edit which column to count..
0
 
LVL 54

Expert Comment

by:Rgonzo1971
then try
Range("C3").CurrentRegion.Columns(3).SpecialCells(xlCellTypeVisible).Count - 1

Open in new window

0
 

Author Comment

by:RWayneH
Yes I tried this with the (3), and a 2, 4 5,  MsgBox returns the same value no matter what number is in it.  Any ideas?
0
 

Author Comment

by:RWayneH
All I am doing is highlighting a range starting in C3, and I am looking for a visible count of a column.  Would like to do a count on column E and H.
0
 
LVL 54

Expert Comment

by:Rgonzo1971
Could you send a dummy?
0
 

Author Comment

by:RWayneH
See attached.
SampleA.xlsm
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
If Skype for Business came with your office 2016 or office 365 installation, you may find that it's almost impossible to either disable or remove it. The application will often launch with each start of Windows, even when explicitly configured not t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

591 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