Excel resize selection to used range

Question

I have a macro that loops through all cells in the selected range and does something to each cell. In this example it will uppercase the contents of each cell.

The problem is, if the user selects an entire column this takes a long time to complete. I was hoping to have a way to shrink the rng to be within the sheet .UsedRange. That way it won't attempt to loop through all 1,048,576 cells in the column.

How can I resize the rng to be within used cells on the sheet of the selection?

Public Sub Uppercase_Selected_Cells()

    Dim rng As Range

    Set rng = Selection
    
    '--- Reduce rng to be within the used cells here ---
    
    For Each cell In rng
        If Not cell.HasFormula Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
    
End Sub

BigBen BigBen · Accepted Answer · 2021-02-18T22:09:42

Use Intersect on Selection and the UsedRange and make sure to test that the result is not Nothing:

Set rng = Intersect(Selection, ActiveSheet.UsedRange)

If Not rng Is Nothing Then
    ' keep processing
End If

Excel resize selection to used range

1 Answers