Working with "selection" instead of precise address= slow as a snail

[ValerieT]

Hello

I want something simple: example change the caps in a column. If I do it the way I know, with an exact address; it is instantaneous:


For i = 2 To EndLine
Cells(i, 2) = WorksheetFunction.Proper(Trim(Cells(i, 2).Value))
Next i
I want to do the same, but using user selection. Never worked with that, so I search in Internet and end with:


Dim rng As Range, c As Range
Set rng = Selection

For Each c In rng
c.Value = WorksheetFunction.Proper(Trim(c.Value))
Next c

it works, but there is performance issue, it take 30 seconds to do the same column as in previous sample. I made a mistake? or is there a better way to "loop" into a selection? I tried offset but not successfully

[Bob Phillips]

This was 70 times quicker in my tests, but I do assume a single column


Application.ScreenUpdating = False

Dim rng As Range, c As Range
Set rng = Selection

With rng

.Offset(0, 1).EntireColumn.Insert
.Cells(1, 2).FormulaR1C1 = "=PROPER(TRIM(RC[-1]))"
.Cells(1, 2).AutoFill .Cells(1, 2).Resize(.Rows.Count)
.Cells(1, 2).Resize(.Rows.Count).Value = .Cells(1, 2).Resize(.Rows.Count).Value
.EntireColumn.Delete
End With

[Bob Phillips]

Not as quick, but for multiple columns


Dim nCols As Long

Application.ScreenUpdating = False

Dim rng As Range, c As Range
Set rng = Selection
nCols = rng.Columns.Count

With rng

.Offset(0, nCols + 1).Resize(, nCols).EntireColumn.Insert
.Cells(1, nCols + 1).Resize(, nCols).FormulaR1C1 = "=PROPER(TRIM(RC[-" & nCols & "]))"
.Cells(1, nCols + 1).Resize(, nCols).AutoFill .Cells(1, nCols + 1).Resize(.Rows.Count, nCols)
.Cells(1, nCols + 1).Resize(.Rows.Count, nCols).Value = .Cells(1, nCols + 1).Resize(.Rows.Count, nCols).Value
.EntireColumn.Delete
End With

Application.ScreenUpdating = True

[ValerieT]

My example was for the worst (full column(s)), but selection can be anything (single cells, range in the middle of no where) . So can't use this kind of solution (but I've learn some new stuff thanks!)

Let turn the problem : is there a way to set 4 variables = to the boundary of a range?
I started by using Mid, Left etc.. on the address, but it is no so easy (for me) as the format is never the same:
$A:$A
$A$2:$ZZ$9999

[westconn1]

is there a way to set 4 variables = to the boundary of a range?

x1 = rng.cells(1).column
y1 = rng.cells(1).row
x2 = rng.cells(rng.cells.count).column
y2 = rng.cells(rng.cells.count).row

though i am not sure how that will help you, especially if you are working with a non-contiguous range

note if your selection is an entire column, you may be processing a heap of empty cells

[ValerieT]

Hello Westconn1, precious helper


if your selection is an entire column, you may be processing a heap of empty cells

Yeap, that is my idea why it is so long, so I guess I could compare the selection to my known last line, and keep the smallest one as boundary.. Anyway thanks for the code, I couldn' get it right with the lines... I close the topic for now, the time for me to test if what I do makes sense.