User Macro causing problem when entire worksheet is selected (all cells)

[anthonya2369]

Ok, I am struggling with a solution to this. I write a lot of code in VBA. I have a current macro that rounds the cells selected by the user by the number of digits selected. I also limited it to the cells that have values that were numeric. Everything runs fine if you select just the range you definitely need. However, some users decided that they want to select the entire worksheet to run the code which makes Excel run through all cells and locks up. Here is my code:

Sub AddRounding(ByVal dblNumber As Double)

    Dim iCell   As Range
    Dim nCount  As Integer

    For Each iCell In Selection
        If IsEmpty(iCell) = False Then
            If IsNumeric(iCell.Value) Then
                If Left(iCell.Formula, 1) = "=" Then
                    iCell.Formula = "=Round(" & Right(iCell.Formula, Len(iCell.Formula) - 1) & "," & dblNumber & ")"
                Else
                    iCell.Formula = "=Round(" & iCell.Formula & "," & dblNumber & ")"
                End If
            Else
                nCount = nCount + 1
            End If
        End If
    Next iCell

    If nCount > 0 Then
        MsgBox nCount & " cell(s) were skipped due to the cell value not being numeric.", vbCritical
    End If

End Sub

What I need to do is, if the user has the entire worksheet selected (all cells even those not being used), I do not allow the code to run. How can I determine this or is there a standard to prevent a cell by cell macro from running when the entire worksheet is selected? Any input is greatly appreciated.

 

[lenze]

Have you thought of using the UsedRange property??

For Each iCell in ActiveSheet.UsedRange

lenze

 

[njimack]

Maybe stick the following code at the start...

If Selection.Count> 100000 Then 'choose appropriate number based on size of used range
Msgbox "Please just select the relevant cells"
Exit Sub
End If

 

[VoG]

Try

For Each icell In Intersect(ActiveSheet.UsedRange, Selection)

 

[doofusboy]

Selection.Count will tell you how many cells have been selected. You could wrap you code with an if statement that doesn't let it run if more than 'X' cells are selected. Of course you can vary the value of 'X'.

 

[anthonya2369]

Thanks for all the replies. Learned something new today! I used the code provided by VoG since it utilizes the usedrange within the selection only. If I chose just the usedrange without the intersect, i believe it would have done all cells and not the ones within the selection.

For Each icell In Intersect(ActiveSheet.UsedRange, Selection)

Another reason I chose this instead of the selection.count item, is that I didn't have to have it stop the macro when it got to a predetermined number, etc. This makes it more user friendly.

Again, thanks for the help!