Merge Rows in VBA

J

Jeff

Hello,

Is it possible in VBA to merge rows that contain a mutual text but different
value ?
Ex:
Column: A B C
ROW 1 "paid" $300.00
ROW 2 "paid" $200.00

Can I merge row 1 and row 2 in row 3 and to do a sum in Column C?
Row 3 "paid" $500.00
Jeff,
 
F

firefytr

Maybe you could use something like this ...


Code
-------------------
Option Explicit
Sub transferMe()
Dim ans As Double, lastRow As Long, i As Long
lastRow = Range("A65536").End(xlUp).Row
ans = 0
For i = lastRow To 1 Step -1
If Range("A" & i).Value = "paid" Then
ans = ans + Range("B" & i).Value
If WorksheetFunction.CountIf(Range("A1:A" & lastRow), "paid") <> 1 Then
Range("A" & i).EntireRow.Delete
Else
Range("B" & i).Value = ans
End If
End If
Next i
End Su
 
F

firefytr

This will do both paid and unpaid.. ..


Code
-------------------
Option Explicit
Sub transferMe()
Dim ans As Double, lastRow As Long, i As Long, negans As Double
lastRow = Range("A65536").End(xlUp).Row
ans = 0
For i = lastRow To 1 Step -1
If Range("A" & i).Value = "paid" Then
ans = ans + Range("B" & i).Value
If WorksheetFunction.CountIf(Range("A1:A" & lastRow), "paid") <> 1 Then
Range("A" & i).EntireRow.Delete
Else
Range("B" & i).Value = ans
End If
End If
If Range("A" & i).Value = "unpaid" Then
negans = negans + Range("B" & i).Value
If WorksheetFunction.CountIf(Range("A1:A" & lastRow), "unpaid") <> 1 Then
Range("A" & i).EntireRow.Delete
Else
Range("B" & i).Value = negans
End If
End If
Next i
End Sub

-------------------


Please note, these sub routines will delete the entire row of ever
match until there is only 1 value of 'paid' and 'unpaid' un column A.
It also assumes your data starts in row 1 and goes down
 
J

Jeff

Thank you for your help.

One more thing. What about if I wanted to have your VBA formula but more
generi: Let's say I don't know what the common text is, but I still want to
merge all rows with common text in colum "A"
is that possible ?
Thanks,
Jeff
 
M

Myrna Larson

I'm not sure you need VBA for this at all. Won't SUMIF do what you want? Or,
if you have two criteria, a SUMPRODUCT formula or array formula? If any of
those worksheet-function solutions will do, they will be much faster than VBA.


Thank you for your help.

One more thing. What about if I wanted to have your VBA formula but more
generi: Let's say I don't know what the common text is, but I still want to
merge all rows with common text in colum "A"
is that possible ?
Thanks,
Jeff
Click to expand...
 
Top