asked on

Using range instead of selection (VBA)

Dear Experts:

below macro cleans and trims selected cells. Everything works fine.

Could somebody tweak / re-write this code please so that ...

1) ... I do not have to activate the "3_Results" Sheet first (ie. the sheet's name "3_Results" should be coded into line 7 and if possible ...
1) ... the range object is applied and not the selection object.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas

Sub SelectRange()
Dim rng As Range
Dim Area As Range

Sheets("3_Results").Activate

Range("D5:D" & Range("A" & Rows.Count).End(xlUp).Row).Select

'Weed out any formulas from selection
  If Selection.Cells.Count = 1 Then
    Set rng = Selection
  Else
    Set rng = Selection.SpecialCells(xlCellTypeConstants)
  End If

'Trim and Clean cell values
  For Each Area In rng.Areas
    Area.Value = Evaluate("IF(ROW(" & Area.Address & "),CLEAN(TRIM(" & Area.Address & ")))")
  Next Area
  
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Norie

There's a typo in what I posted, this,

With Sheets("3_Results")
        Set rng = Range("D5:D" & Range("A" & Rows.Count).End(xlUp).Row)
    End With

Open in new window

should be this.

With Sheets("3_Results")
        Set rng = .Range("D5:D" & .Range("A" & Rows.Count).End(xlUp).Row)
    End With

Open in new window

Andreas Hermle

ASKER

Dear both, the codes work fine and are identical. Norie just had a slight typo and was quick, therefore the majority of the points are awarded to him. I hope I distributed the points equitably.

Thank you very much for your valuable help. I really appreciate it. Regards, Andreas