Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Table sprting
I was bored so I modified Tom Ogilvy's code to filter out empty lines
as well as handle lines that do not have a "lead". 'Modification of Tom Ogilvy's ABC Sub ABC() Dim rw As Long, i As Long Dim cell As Range Cells.Sort Key1:=Range("B1"), Header:=xlNo rw = Cells(Rows.Count, 1).End(xlUp).Row Columns(1).Insert For i = rw To 2 Step -1 Set cell = Cells(i, 3) If Application.WorksheetFunction.CountA(cell.EntireRo w.Cells) = 0 Then Rows(i).Delete Set cell = Cells(i, 3) End If If cell < cell.Offset(-1, 0) Then Rows(i).Insert Cells(i, 1) = Cells(i + 1, 3) If Cells(i, 1) = "" Then Cells(i, 1) = "No Lead" End If End If Next Rows(1).Insert Cells(1, 1) = Cells(2, 3) End Sub Cheers, Jason Lepack Tom Ogilvy wrote: The things you describe are already taken care of by the macro. If what you really mean is that you have Sections or Regions of Data that need to be handled separately, then that would require adjustments. Rightnow, the macro handles your example data and should scale to anysize data as long as it is contiguous. -- Regards, Tom Ogilvy "Sjakkie" wrote: this works to a point. problem is that the list goes on and on and there could be 20 under one lead, 44 under the second, only 2 under the 3rd, 18 under the 4th etc etc. How can this script be made dynamic so that it can still sort them........ "Tom Ogilvy" wrote: Sub ABC() Dim rw As Long, i As Long Dim cell As Range Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlNo rw = Cells(Rows.Count, 1).End(xlUp).Row Columns(1).Insert For i = rw To 2 Step -1 Set cell = Cells(i, 3) If cell = "" Then Stop If cell < cell.Offset(-1, 0) Then Rows(i).Insert Cells(i, 1) = Cells(i + 1, 3) End If Next Rows(1).Insert Cells(1, 1) = Cells(2, 3) End Sub -- Regards, Tom Ogilvy "Sjakkie" wrote: I have a table below that i would like to format using a module. 1 2 3 4 5 a name1 lead1 info1 tel1 add1 b name2 lead1 info2 tel2 add2 c name3 lead2 info3 tel3 add3 d name4 lead3 info4 tel4 add4 e name5 lead2 info5 tel5 add5 How can i format this into 1 2 3 4 5 6 a lead1 b name1 lead1 info1 tel1 add1 c name2 lead1 info2 tel2 add2 d lead2 e name3 lead3 info3 tel3 add3 f name5 lead3 info5 tel5 add5 g lead3 f name4 lead2 info4 tel4 add4 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivit table- cell on spread sheet referencing pivot table field | Charts and Charting in Excel | |||
entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions | |||
VBA Code for a pivot table to open database and to reference table current page | Excel Programming | |||
create a source table from a special pasted value dynamic crossed table | Excel Programming |