Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows to get in a format.
(cross-posting from general Question)
Hi: I am an analyst working in service quality for a market research firm. We use SPSS 12 to output the data and later copy paste the tables into Excel for formatting and printing. The problem I currently have is that I got a large group of output tables which come in two different formats. The first table is like this: Satisfacción Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 Más de 100.001 UF 4,3 2,3 No Responde 5,3 1,6 Group Total 5,3 1,5 Calidad y Certificacion 5,3 1,7 Comercialización 5,7 1,2 TecnologÃ*as 5,2 1,6 Gestión 5,5 1,1 Exp. - PL - CG - ns/nr 6,0 , Group Total 5,3 1,5 Media 6,0 1,2 Tecnico Profesional 5,2 1,6 Universitario 5,2 1,4 Postgrado 5,3 1,6 Group Total 5,3 1,5 And the second: Sexo Masculino Femenino Tamaño de Empresa Menos de 2400 UF 2401 a 25 mil UF 25.001 a 100 mil UF Más de 100.001 UF No Responde Tema del Proyecto (recod) Calidad y Certificacion Comercialización y Marketing TecnologÃ*as de Información Gestión Exp. - PL - CG - ns/nr Nivel educacional Media Tecnico Profesional Universitario Postgrado As you may see, the Group Total rows in the first table are the ones which mess the formatting. I tried to make a macro to delete the first three Group Total Rows and keep the last, but it worked fixedly on the row number. I need a Macro that will start at the beginning of the table and fish out the first three rows with group total, something like this: Sub ProjectTabs() ' ' ProjectTabs Macro ' Macro grabada el 25/10/2004 por Juan Eduardo GorigoitÃ*a ' ' Range("B6:D6").Select Selection.Delete Shift:=xlUp Range("B11:D11").Select Selection.Delete Shift:=xlUp Range("B16:D16").Select Selection.Delete Shift:=xlUp Range("D21").Select End Sub But which works :) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows to get in a format.
Try following..
it's quite fast provided the original list hasn't got 500 group lines. I assume the A3 is always the first data cell I also assume the table is surrounded by empty cells so the CurrentRegion will not pickup extraneous data. Sub KillGroupLines() Dim rng, r& With ActiveSheet.Range("A3").CurrentRegion 'init the union with the empty cell 'below the currentregion Set rng = .Rows(.Rows.Count + 1) For r = 1 To .Rows.Count If UCase$(.Cells(r, 1)) Like "GROUP TOT*" Then Set rng = Union(rng, .Rows(r)) End If Next 'remove the "init" cell Set rng = Intersect(rng, .Cells) If Not rng Is Nothing Then rng.Delete Shift:=xlUp End If End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool ?B?SnVhbiBFZHVhcmRvIEdvcmlnb2l0w61h?= wrote: SatisfacciÇün Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 MÇ*s de 100.001 UF 4,3 2,3 No Responde 5,3 1,6 Group Total 5,3 1,5 Calidad y Certificacion 5,3 1,7 ComercializaciÇün 5,7 1,2 TecnologÇðas 5,2 1,6 GestiÇün 5,5 1,1 Exp. - PL - CG - ns/nr 6,0 , Group Total 5,3 1,5 Media 6,0 1,2 Tecnico Profesional 5,2 1,6 Universitario 5,2 1,4 Postgrado 5,3 1,6 Group Total 5,3 1,5 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows to get in a format.
Hey thanks, it worked!
However, how can it make keep going down deleting the rows with Group Tot* on it? As it is deletes the first group, not the others Thanks! "keepITcool" wrote: Try following.. it's quite fast provided the original list hasn't got 500 group lines. I assume the A3 is always the first data cell I also assume the table is surrounded by empty cells so the CurrentRegion will not pickup extraneous data. Sub KillGroupLines() Dim rng, r& With ActiveSheet.Range("A3").CurrentRegion 'init the union with the empty cell 'below the currentregion Set rng = .Rows(.Rows.Count + 1) For r = 1 To .Rows.Count If UCase$(.Cells(r, 1)) Like "GROUP TOT*" Then Set rng = Union(rng, .Rows(r)) End If Next 'remove the "init" cell Set rng = Intersect(rng, .Cells) If Not rng Is Nothing Then rng.Delete Shift:=xlUp End If End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool ?B?SnVhbiBFZHVhcmRvIEdvcmlnb2l0w61h?= wrote: SatisfacciÇün Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 MÇÂ*s de 100.001 UF 4,3 2,3 No Responde 5,3 1,6 Group Total 5,3 1,5 Calidad y Certificacion 5,3 1,7 ComercializaciÇün 5,7 1,2 TecnologÇðas 5,2 1,6 GestiÇün 5,5 1,1 Exp. - PL - CG - ns/nr 6,0 , Group Total 5,3 1,5 Media 6,0 1,2 Tecnico Profesional 5,2 1,6 Universitario 5,2 1,4 Postgrado 5,3 1,6 Group Total 5,3 1,5 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting rows to get in a format.
Excuse my spanish <g
i hadn;t understood the list keeps going beyond what you described. To adapt for cleaning your the whole sheet (deleting rows where "Group Tot" appears.. Change the line With ActiveSheet.Range("A3").CurrentRegion To With activesheet.usedrange (if it's a freshly imported file...) With Range("C1", Range("A65536").End(xlUp)) (if column A is the one filled at the "bottom" else do it like With Range("A1", Range("C65536").End(xlUp)) if "B" holds the last data row With Range("A1", Range("B65536").End(xlUp)).Resize(,3) this will clear all the tables of ""TYPE1"" form the active sheet. What should happen exactly to the "Second table" as I said... No habla Espanol <g keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool ?B?SnVhbiBFZHVhcmRvIEdvcmlnb2l0w61h?= wrote: Hey thanks, it worked! However, how can it make keep going down deleting the rows with Group Tot* on it? As it is deletes the first group, not the others Thanks! "keepITcool" wrote: Try following.. it's quite fast provided the original list hasn't got 500 group lines. I assume the A3 is always the first data cell I also assume the table is surrounded by empty cells so the CurrentRegion will not pickup extraneous data. Sub KillGroupLines() Dim rng, r& With ActiveSheet.Range("A3").CurrentRegion 'init the union with the empty cell 'below the currentregion Set rng = .Rows(.Rows.Count + 1) For r = 1 To .Rows.Count If UCase$(.Cells(r, 1)) Like "GROUP TOT*" Then Set rng = Union(rng, .Rows(r)) End If Next 'remove the "init" cell Set rng = Intersect(rng, .Cells) If Not rng Is Nothing Then rng.Delete Shift:=xlUp End If End With End Sub keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool ?B?SnVhbiBFZHVhcmRvIEdvcmlnb2l0w61h?= wrote: SatisfacciÇün Expectativas Mean Std Deviation Masculino 5,4 1,4 Femenino 3,8 2,2 Group Total 5,3 1,5 Menos de 2400 UF 5,9 ,9 2401 a 25 mil UF 5,6 1,2 25.001 a 100 mil UF 5,0 1,6 MÇÂ*s de 100.001 UF 4,3 2,3 No Responde 5,3 1,6 Group Total 5,3 1,5 Calidad y Certificacion 5,3 1,7 ComercializaciÇün 5,7 1,2 TecnologÇðas 5,2 1,6 GestiÇün 5,5 1,1 Exp. - PL - CG - ns/nr 6,0 , Group Total 5,3 1,5 Media 6,0 1,2 Tecnico Profesional 5,2 1,6 Universitario 5,2 1,4 Postgrado 5,3 1,6 Group Total 5,3 1,5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |