Hi i have sent with attachment
Try: Sub test1() Dim LR As Long, i As Long, j As Long, k As Long LR = Range("A" & Rows.Count).End(xlUp).Row If Cells(1, 1).Value <> "" Then Cells(1, 1).EntireRow.Insert Results = Array("Address :", "Area :", "Pin :", "Phone :") Columns("A:A").Copy Destination:=ActiveSheet.Range("B1") For p = 0 To UBound(Results, 1) Step 1 Columns("B:B").Replace What:=Results(p), Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Next j = 1 k = 7 For i = 1 To LR With Range("B" & i) If .Value = "" Then j = j + 1 k = 7 Else k = k + 1 Cells(j, k).Value = .Value End If End With Next i Columns("B").Delete Cells(1, 7) = "Company Name" Cells(1, 8) = "Address" Cells(1, 9) = "Area" Cells(1, 10) = "Pincode" Cells(1, 11) = "Phone No" Columns("A:K").AutoFit End Sub Thanks Mahesh On Mon, Jun 20, 2011 at 8:52 PM, ICWAI Help <icwai.answ...@gmail.com> wrote: > Can some one please provide the VB code or somehitng automationi for all > this processure > > > On Mon, Jun 20, 2011 at 5:16 PM, Vasant <vasant...@gmail.com> wrote: > >> use copy, and then paste special with transpose >> >> On Mon, Jun 20, 2011 at 3:46 PM, kannan excel <kannan.ex...@gmail.com>wrote: >> >>> Hi Guru!!!! >>> >>> >>> Pls help me.... i am waiting for your reply. >>> >>> >>> regards >>> >>> Kannan V >>> 9941077703 >>> onechennaiproperty.com >>> >>> -- >>> >>> ---------------------------------------------------------------------------------- >>> Some important links for excel users: >>> 1. Follow us on TWITTER for tips tricks and links : >>> http://twitter.com/exceldailytip >>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >>> 3. Excel tutorials at http://www.excel-macros.blogspot.com >>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >>> >>> To post to this group, send email to excel-macros@googlegroups.com >>> >>> <><><><><><><><><><><><><><><><><><><><><><> >>> Like our page on facebook , Just follow below link >>> http://www.facebook.com/discussexcel >>> >> >> >> >> -- >> Regards >> >> Vasant >> >> -- >> >> ---------------------------------------------------------------------------------- >> Some important links for excel users: >> 1. Follow us on TWITTER for tips tricks and links : >> http://twitter.com/exceldailytip >> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >> 3. Excel tutorials at http://www.excel-macros.blogspot.com >> 4. Learn VBA Macros at http://www.quickvba.blogspot.com >> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com >> >> To post to this group, send email to excel-macros@googlegroups.com >> >> <><><><><><><><><><><><><><><><><><><><><><> >> Like our page on facebook , Just follow below link >> http://www.facebook.com/discussexcel >> > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel