Forum Discussion
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 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
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
2 Replies
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.
