count of values in a column of variable size

[xzmike]

In VBA I'm trying to get a count of date values in a column(in this case column A) when the count may vary.

The sheet may look like
Salary or Salary
1/1/04 1/1/04
1/10/04 1/10/04
1/17/04

Sum Sum


I'm not sure ahead of time which sheet I'l get. In the first case I'd want a count of 2, in the second, 3.

I tried selecting the currentregion (shown), but I it included the Salary Column but that included the Salary Column
Selection.currentregion.Select
Dim myRegion As Range
Set myRegion = Selection
MsgBox myRegion.Columns(1).Cells.Count


thanks for any help.
Mike

 

[xzmike]

I figured this one out myself. Much simler answer. Have to occasionally step back to see the trees for the forest. No need to reply. Is there any way to unpost the message before any replies are sent in?

thanks,
Mike

 

[Tom Urtis]

Is there any way to unpost the message before any replies are sent in?

Yes, by clicking the "X" (Delete this post) button to the right of the Edit and Quote buttons on your initial thread in the left column. However, some people may be interested in what solution you chose because this is a public forum, so it'd also be a nice gesture if you share it with everyone now that you raised the curiosity on this (rainy in California) New Years Day.

 

[xzmike]

The simpler solution I came up with (assume I've named Range A3 as firstpayrldt):

Dim payrlCnt As Integer
payrlCnt = 0

Range("firstpayrldt").Select

Do Until ActiveCell.Value = ""
payrlCnt = payrlCnt + 1
ActiveCell.Offset(1, 0).Select
Loop

 

[NateO]

Hello Mike,

The simpler solution I came up with (assume I've named Range A3 as firstpayrldt):

Dim payrlCnt As Integer
payrlCnt = 0

Range("firstpayrldt").Select

Do Until ActiveCell.Value = ""
payrlCnt = payrlCnt + 1
ActiveCell.Offset(1, 0).Select
Loop

Hmmm, a few nitpicks... I don't like the do loop versus the use of a collection, ="", selecting cells, or As Integer. Try the following:

MsgBox [counta(firstpayrldt)]

 

[xzmike]

I tried that, Nate. I got a count of one. The range of firstpayrldat is only A3. I don't know in advance how big my range(column) of payroll dates will be.

Mike

 

[NateO]

Fair enough Mike, how about the following:

MsgBox WorksheetFunction.CountA(Range([firstpayrldt], _
    Cells(65536, [firstpayrldt].Column).End(3)))

Bon chance.

 

[xzmike]

Thanks for the formula, Nate. I just changed the Counta to Count because I just wanted dates, not text.

The formula:
WorksheetFunction.Count(Range([firstpayrldt], _
Cells(65536, [firstpayrldt].Column).End(3)))

Could you explain the argument: Cells(65536, [firstpayrldt].Column).End(3))) for me?

My understanding is the first part:Range([firstpayrldt], evaluates to A3.

The next part I get Cells(65536, which is the last cell in the lower right-hand size of the worksheet.
Then the
[firstpayrldt].Column).End(3))) I'm not sure how to interpret that--last data row in Column A-- in conjunction with the Cells(65536

Thanks for any explanation
Mike

 

[NateO]

Hello again Mike,

65536 is the last row eh, not bottom right corner. In any case you're referencing an item withing the cells range object of a worksheet object. The shorthand looks like this:

Cells(row,column)

The long-hand could look like:

MsgBox Cells.Item(1, 2).Address

But you don't want row 65536, you want the last populated row. So, use the end method as you would with a keboard. Check out the help file. 3 is simpy an acceptable constant for xlUp, which is what your macro recorder would give you.

Hope this helps.