excel aba variable range help

[DJ2018]

Hi Guys,

I need to remove blanks from a variable range e4:g - variable ive managed to replace the blanks but only on a fixed range.
here is what I have.

Private Sub cmdClean_Click()
    Const sBad      As String = "`!@#$;^()_-=+{}&[]\|;:'"",.<>/?"
    Dim cell        As Range
    Dim s           As String
    Dim i           As Long

    Application.ScreenUpdating = False
    For Each cell In Range("E4:G50")
        If VarType(cell.Value) = vbString Then
            s = cell.Value

            For i = 1 To Len(sBad)
                s = Replace(s, Mid(sBad, i, 1), " ")
            Next i
            
            cell.Value = Application.Trim(s)
        End If
    Next cell
    
    
    For Each c In Worksheets("Retso").Range("e4:G15")
        If c.Value = "" Then
            c.Value = "TEST"
        End If
    Next c
              
    Application.ScreenUpdating = True
End Sub

 

[combo]

You can refer via Cells and use row/column variables or build range string:

Dim rng As Range, strRng
Set rng = Range(Cells(2, 2), Cells(5, 5))
MsgBox rng.Address
' or
strRng = "b2:e" & 5
Set rng = Range(strRng)
MsgBox rng.Address

Refering to worksheet too, using cells:

Dim rng As Range
With Worksheets("Retso")
    Set rng = Range(.Cells(2, 2), .Cells(5, 5))
End With

combo

 

[SkipVought]

Hi,

    Dim rng As Range
    
    With ActiveSheet
        Set rng = Range(.Cells(4, 5), .Cells(.UsedRange.Rows.Count + .UsedRange.Row - 1, 7))
    
        For Each cell In rng
'...
        Next Cell
    End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}