Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
For FARAZ QURESHI - Column Entry Counts
The board is not operating properly, cannot find or reply to your request to:
select column, count & report items in it, move to next column based on user choice and repeat - but here is code that I believe will do what you want. I hope so, and I hope you see this message. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
average daily counts to weekly counts | Excel Discussion (Misc queries) | |||
Auto entry of data based on entry of text in another column or fie | Excel Discussion (Misc queries) | |||
eliminate data entry of counts by state | New Users to Excel | |||
Column Counts | Excel Discussion (Misc queries) | |||
Looking for a Function which counts the repeatition of text in a column | Excel Worksheet Functions |