Apply expression to entire range

[Tod]

Here's an easy one for somebody.

My code currently cycles through a column in a worksheet
and removes unwanted characters, like this:

For Each Cell In mySheet.Range("A2:A" & mySheet.Range
("A65536").End(xlUp).Row)
If Left(Cell, 1) = "=" Or _
Left(Cell, 1) = "-" Or _
Left(Cell, 1) = "+" Or Then
Cell.Value = "'" & Cell.Value
End If
Next Cell

Problem is that if the worksheet has several thousand
records this code could take minutes. Is there a way to
apply this to the entire range at once instead of cycling
the range?

tod

 

[Bob Phillips]

You can make it quicker

application.Screenupdating = False
Application.Calculation = xlCalculationManual

cLastRow = mySheet.Cells(Rows.Count,"A").End(xlUp).Row
For Each Cell In mySheet.Range("A2:A" & cLastRow)
tmp = Left(Cell, 1)
If tmp = "=" Or tmp = "-" Or tmp = "+" Then
Cell.Value = "'" & Cell.Value
End If
Next Cell


application.Screenupdating = True
Application.Calculation = xlCalculationAutomatic

 

[Dana DeLouis]

Just guessing, but would you be trying to clear formulas, and numbers I am
not sure of what data you have, so I'll just throw out this idea.

Sub Demo()
ActiveSheet.UsedRange
On Error Resume Next
With Columns("A:A")
.SpecialCells(xlCellTypeFormulas).ClearContents
.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
End With
End Sub

HTH

 

[Tushar Mehta]

It is not clear what the intent is because the code as shown doesn't
remove any characters. Further, by not specifying which property to
use in the If statement, you are relying on the default property of the
range object. Effectively you are checking Left(Cell,1).value.

So, the only way you can get '=' or '+' in the value of a cell is if
the cell contains text.

In any case, the code below duplicates as closely as I can figure out
your code and takes about 2 seconds to execute for 65,535 rows of data.
It uses a spare column as a scratch workarea (column B in this case) to
have XL do the work for us. Then, it copies the result back into
column A and clears the contents of the scratch area.

Sub Macro2()
Dim StartTime As Date
StartTime = Now()
Range("b1").FormulaArray = _
"=IF(ISBLANK(RC[-1]),"""",IF(OR(LEFT(RC[-1],1)=
{""="",""-"",""+""}),""'""&RC[-1],RC[-1]))"
Range("b1").AutoFill Columns("b")
Columns(2).Copy
Columns(1).PasteSpecial xlPasteValues
Columns(2).Clear
MsgBox Format(Now() - StartTime, "hh:mm:ss.s")
End Sub

[watch out for line breaks in the post.]

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions