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 |
All times are GMT +1. The time now is 02:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com