Just A Small Code
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. -- Best Regards, FARAZ A. QURESHI |
Just A Small Code
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 "FARAZ QURESHI" wrote: 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. -- Best Regards, FARAZ A. QURESHI |
All times are GMT +1. The time now is 12:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com