Dec 28 2021 10:49 AM
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
Dec 28 2021 11:37 AM
SolutionI'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
Dec 28 2021 12:52 PM
Dec 28 2021 11:37 AM
SolutionI'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