VBA: Code to skip or ignore rule If Cell data is not Identical

[unknownymous]

Hi Gurus,

Good day!

I just wanted to ask for your insight as I found a code to merge accounts, however if the cell data is not the same, it will skip the code.

Example:

No	Name	Data 1	Data 2
001	Math	100	95
002	Math	100	95
003	Science	95	90

'VBA Code for Merging the Data 1 and Data 2

Dim i As Long

'Row Selection

Rows("2:3").Select

' Go or Ignore
For i = 3 To Selection.Columns.Count
Selection.Cells(1, i) = Selection.Cells(1, i) + Selection.Cells(2, i)
Next i
Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp

End Sub

= = = =

In the initial code, I think I need to check first if cell B2 & B3 are both "Math". If yes, the code will push through but if the cell text is different then ignore .

Appreciate the help.

 

[Micron]

maybe

Dim i As Long, n As Long

Rows("2:3").Select 'Row Selection
For n = 1 To 3
  If Selection.Cells(n, 2) = Selection.Cells(n + 1, 2) Then
    For i = 3 To 6 'Selection.Columns.count
      Selection.Cells(1, i) = Selection.Cells(1, i) + Selection.Cells(2, i)
    Next i
  End If
Next n
    
Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp

NOTES:
Do you really want to loop over 16,384 columns?
I used 6 columns and 3 rows as a test. However, re: rows, I think you'd want to use a descending counter that is = to a variable that is the number of used rows to 2 (?) step -1 and adjust the variable value after each row is removed. I haven't used loop counters based on depleting rows for a long time - if ever, so I might be a bit fuzzy on that logic. Easy enough to Google though.

 

[unknownymous]

This is gem! Thank you

 

[unknownymous]

maybe

Dim i As Long, n As Long

Rows("2:3").Select 'Row Selection
For n = 1 To 3
  If Selection.Cells(n, 2) = Selection.Cells(n + 1, 2) Then
    For i = 3 To 6 'Selection.Columns.count
      Selection.Cells(1, i) = Selection.Cells(1, i) + Selection.Cells(2, i)
    Next i
  End If
Next n
   
Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp

NOTES:
Do you really want to loop over 16,384 columns?
I used 6 columns and 3 rows as a test. However, re: rows, I think you'd want to use a descending counter that is = to a variable that is the number of used rows to 2 (?) step -1 and adjust the variable value after each row is removed. I haven't used loop counters based on depleting rows for a long time - if ever, so I might be a bit fuzzy on that logic. Easy enough to Google though.

It's working! Thanks!

 

[unknownymous]

maybe

Dim i As Long, n As Long

Rows("2:3").Select 'Row Selection
For n = 1 To 3
  If Selection.Cells(n, 2) = Selection.Cells(n + 1, 2) Then
    For i = 3 To 6 'Selection.Columns.count
      Selection.Cells(1, i) = Selection.Cells(1, i) + Selection.Cells(2, i)
    Next i
  End If
Next n
   
Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp

NOTES:
Do you really want to loop over 16,384 columns?
I used 6 columns and 3 rows as a test. However, re: rows, I think you'd want to use a descending counter that is = to a variable that is the number of used rows to 2 (?) step -1 and adjust the variable value after each row is removed. I haven't used loop counters based on depleting rows for a long time - if ever, so I might be a bit fuzzy on that logic. Easy enough to Google though.

Apologies, I noticed that even if the text in Cell B2 & C2 is not the same, the macro delete a row. I was wondering to ignore this as well "Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp" if this is the case.

 

[Micron]

Working on it with more data rows than I used before - I think I see another issue; not sure yet...

 

[Micron]

Just moving that line could fix that issue, but if the left side is what you have and the right side is what you want to end up with by removing rows, I don't see how using the Selection method like what you started with could ever work. As rows are removed, the selection doesn't change?

Name	Data 1	Data 2		Name	Data 1	Data 2
Math	100​	95​		Math	175​	190​
Math	75	95​		Science	95​	75​
Science	95	75​		Math	75​	85​
Math	75	85​		Science	180​	185​
Science	85	90​
Science	95	95​

Still didn't answer my question re looping over 16+k columns...
Better to set the counter to the number of used rows/columns in the used range?

 

[unknownymous]

Just moving that line could fix that issue, but if the left side is what you have and the right side is what you want to end up with by removing rows, I don't see how using the Selection method like what you started with could ever work. As rows are removed, the selection doesn't change?

Name	Data 1	Data 2		Name	Data 1	Data 2
Math	100​	95​		Math	175​	190​
Math	75	95​		Science	95​	75​
Science	95	75​		Math	75​	85​
Math	75	85​		Science	180​	185​
Science	85	90​
Science	95	95​

Still didn't answer my question re looping over 16+k columns...
Better to set the counter to the number of used rows/columns in the used range?

Its's all good. I just tweak the code you provided. Thanks again!