QUESTION: hello Sir,
Can you provide me vba code, which will give me actual number of rows in a given range (columns 16) excluding formulas. Though In a given range let say column 16 actually have 10 rows of data, but it has formulas spread over 100 rows. so, rw variable always returns 100. I want that it will return just 10.
I have the following code:
Set sh1 = Worksheets("List of title variations")
lc = sh1.Cells(1, sh1.Columns.Count).End(xlToLeft).Column
For i = lc To 2 Step -1
rw = Application.CountA(sh1.Columns(i))
'Set rp = sh1.Columns(i)
'cntt = rp.Cells.SpecialCells(xlVisible).Rows.Count
Set r1 = sh1.Cells(1, i).Resize(rw, 1)
rwo = sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row
r1.Cut sh1.Cells(rwo + 1, 1)
ANSWER: Hi James,
This gives you the constants count for the cells in column 16 (P):
Does that help?
---------- FOLLOW-UP ----------
QUESTION: Dear Sir,
That did not help me any more?
I tried with following code:
Set sh1 = Worksheets("Concatonated titles")
ccnt = sh1.Range("P:P").SpecialCells(xlCellTypeFormulas).Count
ccnt1 = sh1.Range("P:P").SpecialCells(xlCellTypeConstants).Count
ccnt shows 100 rows available.
But ccnt1 shows 'run time error 1004' with 'No cells were found'
That means it found no cells containing a constant value (either number, boolean, text or error). Empty cells do not count as constant cells. I guess I misread your question, apologies.
The non-formula cells are (taking into account only the used range of the sheet this time):