Just A Small Code

[FARAZ QURESHI]

I want a VBA code for:

1. Select the Complete column of Currently Active Cell;
2. Go to the First Cell of the selected column (like Ctrl + .);
3. Count the number of items present in the selection; and
4. Finally Display the said column's number of items in a popup message;
5. If the selected column was the last one (as determined by Ctrl + End),
then display msgbox "Operation Complete";
6. If it is not the last column of the data, display msgbox "Do you want to
continue?"
7. If pressed Yes, then offset one cell to the right and repeat steps 1-6;
8. If pressed No, then display msgbox, "Operation Complete".

Thanx in advance.

 

[JLatham]

I posted separately for you, as this board is really acting up - just dropped
all 23 DEC entries from view! Here is code that I believe will do what you
want:

Sub CountEntriesInAColumn()
Dim lastUsedColumn As Long
Dim lastUsedRow As Long
Dim currentColumnArea As Range
Dim itemCount As Long
Dim continueFlag As Integer

lastUsedColumn = ActiveCell.SpecialCells(xlLastCell).Column
lastUsedRow = ActiveCell.SpecialCells(xlLastCell).Row
continueFlag = vbYes ' initialize
Do While continueFlag = vbYes
Cells(1, ActiveCell.Column).Activate
Set currentColumnArea = _
ActiveSheet.Range(ActiveCell.Address & ":" & _
Cells(lastUsedRow, ActiveCell.Column).Address)
itemCount = Application.WorksheetFunction. _
CountA(currentColumnArea)
'test if this is the last column
If ActiveCell.Column >= lastUsedColumn Then
'either at last used column or to the right of it
MsgBox "This column has " & _
itemCount & " entries in it." & vbCrLf _
& "It is the last used column.", vbOKOnly, _
"Column Entry Count"
continueFlag = vbNo ' set flag to stop loop
Else
'more columns to examine
continueFlag = MsgBox("This column has " & _
itemCount & " entries in it." & vbCrLf _
& "Do you want to continue?", vbYesNo, _
"Column Entry Count")
If continueFlag = vbYes Then
'move to next column
ActiveCell.Offset(0, 1).Activate
End If
End If
Loop
MsgBox "Operation Complete"
End Sub