SOLVED

For Loop select range to clear contents

Brass Contributor

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 delete the whole row, just clear the contents of specific cells in that row. I can't figure out the syntax to select Columns A-G for Row(r) - where "r" is the row the FOR loop is on. Can you help?

 

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

2 Replies
best response confirmed by RandomPanda (Brass Contributor)
Solution

@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
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.
1 best response

Accepted Solutions
best response confirmed by RandomPanda (Brass Contributor)
Solution

@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

View solution in original post