For Loop select range to clear contents

I have a FOR loop that is combining values with duplicate identifiers in a few different columns but I'm having trouble clearing contents of just the cells I want to get rid of. I don't want to delet...

excel

Macros and VBA

HansVogelaar

Dec 28, 2021

RandomPanda

I'd do it like this:

    For r = m - 1 To 2 Step -1
        If Range("A" & r + 1).Value = Range("A" & r).Value Then
            Range("C" & r).Value = Range("C" & r).Value + Range("C" & r + 1).Value
            Range("E" & r).Value = Range("E" & r).Value + Range("E" & r + 1).Value
            Range("F" & r).Value = Range("F" & r).Value + Range("F" & r + 1).Value
            Range("G" & r).Value = Range("G" & r).Value + Range("G" & r + 1).Value
            Range("A" & r + 1 & ":G" & r + 1).ClearContents
        End If
    Next r

HansVogelaar

MVP

Dec 28, 2021

RandomPanda

I'd do it like this:

    For r = m - 1 To 2 Step -1
        If Range("A" & r + 1).Value = Range("A" & r).Value Then
            Range("C" & r).Value = Range("C" & r).Value + Range("C" & r + 1).Value
            Range("E" & r).Value = Range("E" & r).Value + Range("E" & r + 1).Value
            Range("F" & r).Value = Range("F" & r).Value + Range("F" & r + 1).Value
            Range("G" & r).Value = Range("G" & r).Value + Range("G" & r + 1).Value
            Range("A" & r + 1 & ":G" & r + 1).ClearContents
        End If
    Next r

RandomPanda

Brass Contributor

Dec 28, 2021

Thank you so much for that. If you saw my deleted post already, I figured it out. I guess my other file had a "sort" feature first.
Much appreciated.

Forum Discussion

For Loop select range to clear contents

Resources