ken,
thanks.
one more thing in the rows inserted i have to add some information, in first
vendor i have type the information & formulas and then i copy this after
every change in vendor, the information and formula is :
Vendor =+C23
Company Code 7039
Name =INDEX(data,MATCH(E17,vendor,0),2)
City =INDEX(data,MATCH(E17,vendor,0),4)
what i am doing is after every change in vendor i am copying the above
detail so it gives first vendor information and then vendor line items.
Now my ques is can we add this also in the macro you have given?
This will solve my all problem.
Thanks
"Ken Wright" wrote:
Another option:-
Sub InsRows()
Application.ScreenUpdating = False
Dim numRows As Integer
Dim R As Long
Dim rng As Range
Dim LastRw As Long
numRows = 5
LastRw = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range(Cells(1, "A"), Cells(LastRw, "A"))
For R = rng.Rows.Count To 1 Step -1
With Cells(R, "A")
If .Value < .Offset(1, 0).Value Then
rng.Rows(R + 1).Resize(numRows).EntireRow.Insert
End If
End With
Next R
Application.ScreenUpdating = True
End Sub
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03
------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------
"Ankur" wrote in message
...
i have a worksheet of vendor account details. in one column there are
vendor
numbers & some vendors have 5 line items some have10,15,50,etc i.e. line
items are different. Now the problem is every month i have to prepare
vendors
template and i have to insert 5 rows after every change in vendor number
in
vendor column.
right now i am going manually at each vendor change and inserting rows as
detail above, there are about 700 vendors. this is not only time consuming
but frustating job.
so there any way to develop macro for this or any other way so that by
giving one command the above problem can be sorted out
thanks a lot in advnace.....
|