Question about efficiency

[Moragtao]

Currently, a lot of the macros I run in Excel require the same thing to do be done to multiple cells. For example, the loop below simply adds an L and a space to the beginning of everything in a particular column.

Do Until ActiveCell.Value = ""
     ActiveCell.Value = "L " & ActiveCell.Value
     ActiveCell.Offset(1, 0).Select
Loop

What I'm wondering is, is there a more efficient way for me to go about doing this? I know selecting a cell isn't ideal, but this is the best way I currently know to complete the given task.

CROSSPOSTED:
https://www.excelforum.com/excel-pro...fficiency.html

[mdmackillop]

Sub Test()
Dim cel As Range
For Each cel In Range("L:L").SpecialCells(2)
cel.Value = "L " & cel.Value
Next cel
End Sub

[Paul_Hossler]

This approach can be a little finicky since you have to build the formula string

1. with the double quotes inside a VBA string to represent a 'quote as an embedded character'

2. and there are some 'empty string' double quotes in the formula

So I usually make it easier for me by using the Q = Chr(34) method

I like it since there's no looping required

 
Option Explicit
Sub test()
    Dim sFormula As String, Q As String, QQ As String
    
    Q = Chr(34)
    QQ = Chr(34) & Chr(34)
    
    With ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants)
        s = "IF(" & .Address & "<>" & QQ & "," & _
            Q & "L " & Q & "&" & .Address & " ," & _
            QQ & ")"
        .Value = Application.Evaluate(s)
    End With

End Sub

The .Evaluate method I learned about here a long time ago, but I forgot the originator

[mdmackillop]

Hi Paul
You have a problem if there is a blank cell in the data; however over 10k rows, your is 8 times quicker. If I ever find a nut and need a sledgehammer, I know who to ask.
Regards
Malcolm

[Paul_Hossler]

I made an assumption (from #1) that there were no empty cells (you'd think I'd know better)

This is a more robust approach

Option Explicit
Sub test()
    Dim sFormula As String, Q As String, QQ As String
    Dim rArea As Range
    
    Q = Chr(34)
    QQ = Chr(34) & Chr(34)
    
    For Each rArea In ActiveSheet.Columns(1).SpecialCells(xlCellTypeConstants).Areas
        With rArea
            sFormula = "IF(" & .Address & "<>" & QQ & "," & _
                Q & "L " & Q & "&" & .Address & " ," & _
                QQ & ")"
            .Value = Application.Evaluate(sFormula)
        End With
    Next

End Sub

Edit: For some reason, even with Option Explicit, using the 's' variable was not flagged as an error, even though sFormula was Dim-ed

I've see that happen a few times, but have not figured out why

[mdmackillop]

Edit: For some reason, even with Option Explicit, using the 's' variable was not flagged as an error, even though sFormula was Dim-ed

It errors for me. Have you "s" as a Public variable/constant elsewhere?

[Paul_Hossler]

Capture.JPG

1. If I select the 's' and right-click for Definition, it throws the error box. But it's still run-able and Alt-d compliable

2. However, after running Rob Bovey's code cleaner, everything works as normal

I've never had a short macro with such a little bit of editing get confused.

It was a head scratcher, but thanks for confirming that it shouldn't work