Rick,

I got around my first obstacle by rewriting it. It probably is a goofy way
to do it but it worked. Now, I am unable to work my way through the part
that finds last row in xrng range:

lRow = xRng.End(xlDown).Row

I get a message of "Object variable or With block variable not set."

What do I do to get around this?

Respectfully,

Pearl

"Rick Hansen" wrote:

Good Evening from the Land of Midnight Sun, Pearl.
Sorry about the first, try I clicked to Fast. Anyway, I believe I have
come up with macro solution for project. In a nut shell the code determines
range of the list. Once the ranger has determine lastrow(lrow) , lastcol
(lcol), then range object is set, ie (xRng). Then the Range(xRng) is sorted
with key on columns A & B, thus alphabetical sorted by row. (Note: All Blank
rows are sort towards the bottom of the range, all data is towards the top
of the range). So now all Like data is grouped together. Now with simple
"for loop" and compare routine you add the qty's for all like items. Then
single like item is copied to NewData array with complete qty. Once the For
Loop is complete. The NewData() array is copy to Worksheet "Sheet2",
with topleft cell being at "A3". This down and dirt explaination. If have
questions, please post me post...

enjoy, Rick (Fairbanks, Alaska)




Option Explicit

Sub combineData()

Dim xRng As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lRow As Long
Dim lCol As Integer, iRow As Integer
Dim x As Integer, cnt As Integer
Dim hold As Variant, NewData() As Variant
Dim holdRet As Variant
Dim strhold As String

Set ws1 = ActiveSheet '' I used sheet1
Set ws2 = Worksheets("Sheet2")

Application.ScreenUpdating = False

'' find last item in list row location
lRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
'' find last column location
lCol = ws1.Range("A3").End(xlToRight).Column

'' now set range object
Set xRng = ws1.Range("A3:" & Chr(64 + lCol) & lRow)
holdRet = xRng ''save old list range

'' sort range by col A, & col B
xRng.Sort key1:=ws1.Range("A3"), order1:=xlAscending,
key2:=ws1.Range("B3"), _
order2:=xlAscending, header:=xlGuess, Orientation:=xlSortColumns

'' find last row in xrng range.(Note: all blank rows are bottom of range
xrng)
lRow = xRng.End(xlDown).Row
'' set new diminsion for Newdata array
ReDim NewData(1 To lRow, 1 To lCol)

'' copy xrng into hold variant variable, now hold is variant array.
hold = xRng

iRow = 1 '' row location, in hold() array
cnt = 1 '' row location for NewData() array

Do
'' concatenate to make string
strhold = hold(iRow, 1) & hold(iRow, 2)
'' copy data from hold array to newdata arrray
For x = 1 To lCol
NewData(cnt, x) = hold(iRow, x)
Next x

NewData(cnt, 3) = 0
Do
'' calc the qty's of each like hold names
NewData(cnt, 3) = NewData(cnt, 3) + hold(iRow, 3)
'' point next row in hold()
iRow = iRow + 1
'' don't break hold() boundaries, loop if compare string = to next
row
Loop While (i < UBound(hold, 1) And strhold = hold(iRow, 1) &
hold(iRow, 2))
'' point to next row in NewData()
cnt = cnt + 1
'' don't break hold() boundaries and don't process if next row is empty
Loop While (i < UBound(hold, 1) And hold(iRow, 1) < "")

'' now find lastrow that was used in Newadata()
For x = LBound(NewData, 1) To UBound(NewData, 1)
If NewData(x, 1) = "" Then
Exit For
End If
Next x

xRng.Value = holdRet '' copy old stuff back
Set xRng = Nothing '' clear old range object
'' now set xrng with number rows in Newdata, number col's
Set xRng = ws2.Range("A3").Resize(x, lCol)
' now copy variant array NewData() back to Sheet2
xRng = NewData

End Sub






"Pearl" wrote in message
...
I have a list of data and I need to write a macro that would find rows
which
contained duplicate information (except for the cells containing the
different quantities), combine them into one row entry with the total
quantity for all the duplicate rows and keep only one record. The list
does
contain blank rows and can be quite lengthy with numerous variations of
information which is usually never the same from list to list.

What I have is:
A B1 6 53.25 37.25

B B1 8 34.00 45.75
A B1p 2 53.25 37.25
C GL1 20 34.00 45.75

A B1 14 53.25 37.25


A B1p 7 53.25 37.25
A GL1 10 34.00 45.75
B B1 100 34 45.75
A B1P 18 53.25 37.25

C GL1 5 34.00 45.75
B MP1 10 12.5 18.00

What I would like to have is:
A B1 20 53.25 37.25
A B1p 27 53.25 37.25
A GL1 10 34.00 45.75
B B1 108 34.00 45.75
B MP1 10 12.5 18.00
C GL1 25 34.00 45.75

How do I accomplish this with a macro?