RWayneH
asked on
Get specific column row count within a selected range.
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.Col umns(1).Sp ecialCells (xlCellTyp eVisible). 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.
MsgBox Cells(1).CurrentRegion.Col
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.
ASKER
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?
So if I start at C3, how would I tell it to retrieve the number of visibles in column E?
then try
MsgBox Range("C3").CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
Columns(1) in this case takes the first column of the current region where c3 is included
ASKER
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..
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..
then try
Range("C3").CurrentRegion.Columns(3).SpecialCells(xlCellTypeVisible).Count - 1
ASKER
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?
ASKER
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.
Could you send a dummy?
ASKER
See attached.
SampleA.xlsm
SampleA.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help.
Cells(1) is A1 if you look for 1st column of current region adjacent to A1
then try
Open in new window
REgards