Data arrangement

Try this macro - it assumes that your vendor code starts with 101 - you'll need to edit it if it doesn't.

Sub Test()
Set DataRange = Range(Cells(2, 1), Cells(Cells(65536, 1).End(xlUp).Row, 1))
For Each Cell In DataRange
If Left(Cell, 3) = "101" Then
CurrentVendorCode = Cell
CurrentVendorName = Cell.Offset(0, 1)
Else
Cells(65536, 7).End(xlUp).Offset(1, 0) = CurrentVendorCode
Cells(65536, 7).End(xlUp).Offset(0, 1) = CurrentVendorName
Cells(65536, 7).End(xlUp).Offset(0, 2) = Cell
Cells(65536, 7).End(xlUp).Offset(0, 3) = Cell.Offset(0, 1)
Cells(65536, 7).End(xlUp).Offset(0, 4) = Cell.Offset(0, 2)
End If
Next Cell
End Sub

Originally Posted by kushalprakash

Dear Mr. Mrice,

I am very thankful to you for your valuable guidance about macros. my task is running but some problem still there.

my excel raw data having thousands vendor and this macros not going in ooping till all vendors completed. Also Amount column not merging.

Can I sent you Excel file containg my whole data.

I am very hungry about this task. Please help me.

with warm regards

kps rajput
email: [email protected]

Hi,

Your problem is that you have not really specified how to determine row types that are to be removed from row types that are to be retained.

IF (and only if) the determination is that A len = 6 is to be removed, and others are to be retained then

Please Login or Register  to view this content.

will do that for you.

hth
---