CODEWITHSUNDEEP
vbaexcelperformance
DukeSilver
DukeSilver

Reputation: 748

VBA: edit cell value based on adjacent cell without loop

Currently, I have code that puts an x in a cell if a cell in a different column of the same row is not blank. Something like this:

for i = 2 to lLastRow
    if Cells(i,1) <> "" then cells(i,2) = "x"
next i

But I have a case where my dataset is tens of thousands of rows and that loop is sucking up time. Is there a way to do this without looping?

Upvotes: 3

Views: 1138

Answers (3)

DisplayName
DisplayName

Reputation: 13386

array approach most likely provides the fastest solution:

Option Explicit

Sub main()
    Dim i As Long
    Dim vals As Variant

    With Range("A2", Cells(Rows.Count, 1).End(xlUp))
        vals = .Value
        For i = 1 To UBound(vals)
            If Not IsEmpty(vals(i, 1)) Then vals(i, 1) = "x"
        Next
        .Offset(, 1).Value = vals
    End With
End Sub

while a no-loop solution could be:

Range("A2", Cells(lLastRow,1)).SpecialCells(xlCellTypeConstants).Offset(,1) = "x"

although is most likely to be slow

Upvotes: 1

paul bica
paul bica

Reputation: 10715

Using AutoFilter

Option Explicit

Public Sub ReplaceBlankOffset()
    Dim col1 As Range, col2 As Range
    Set col1 = ActiveSheet.UsedRange.Columns("E")
    Set col2 = ActiveSheet.UsedRange.Columns("F")

    col1.AutoFilter Field:=1, Criteria1:="<>"
    If col1.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        col2.Offset(1).Resize(col2.Cells.Count - 1).FormulaR1C1 = "x"
    End If
    col1.AutoFilter
End Sub

Rows: 500,001 - Time: 1.078 sec

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96763

Give this a shot..............it does not need a loop:

Sub Killer_V2()
    Dim rng2 As Range, rng As Range
    Dim N As Long, s As String
    Dim critCol As String, helpCol As String

    critCol = "A"
    helpCol = "B"
    N = Cells(Rows.Count, critCol).End(xlUp).Row
    Set rng = Range(Cells(1, critCol), Cells(N, critCol))
    s = "=IF(" & rng.Address & "<>"""",""x"","""")"

    Set rng2 = Range(Cells(1, helpCol), Cells(N, helpCol))
    rng2.Value = Evaluate(s)
End Sub

Upvotes: 0

Related Questions