View Full Version : [SOLVED:] Working with "selection" instead of precise address= slow as a snail
ValerieT
07-31-2014, 12:55 AM
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
07-31-2014, 02:45 AM
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
07-31-2014, 02:57 AM
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
07-31-2014, 03:17 AM
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
07-31-2014, 04:17 AM
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
07-31-2014, 05:14 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.