From The Macro I have
'\\ Set number of rows to be retained
Const iCnt As Integer = 3
But I want to use a InputBox to assign the number
'\\ Set number of rows to be retained
inputData = Application.InputBox("Enter # Connections Non-Attendees Must Have:" & vbCrLf & _
"e.g Enter 2 for 3 or more" ,"Input Box Text", Type:=1)
Const iCnt As Integer = inputData
But I get the error "Constant expression required" and do not know how to get around this
Thanks
The Full Macro:
Public Sub DeleteRowsLessThen_n()
Dim vSrc As Variant, vChk() As Variant
Dim i As Long
'\\ Set number of rows to be retained
Const iCnt As Integer = 3
'\\ Build up data in arrays so that we can process faster
With ws.Range("D2:D" & Range("A" & Rows.Count).End(xlUp).Row)
'##Show all data
If ws.FilterMode Then ws.ShowAllData
ReDim vChk(1 To .Rows.Count + 1, 1 To 1)
vSrc = .Value
End With
'\\ Create dictionary object for keeping count
With CreateObject("Scripting.Dictionary")
For i = LBound(vSrc) To UBound(vSrc)
If .Exists(vSrc(i, 1)) Then
.Item(vSrc(i, 1)) = .Item(vSrc(i, 1)) + 1
Else
.Add vSrc(i, 1), 1
End If
Next i
For i = LBound(vSrc) To UBound(vSrc)
If .Item(vSrc(i, 1)) >= iCnt Then
vChk(i, 1) = 1
End If
Next i
End With
'\\ Clean up the data using check done above
On Error Resume Next
With ws.Range("L2:L" & Range("A" & Rows.Count).End(xlUp).Row)
.Value = vChk
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Clear
End With
End Sub
Bookmarks