Deleting rows to get in a format.

[Juan Eduardo Gorigoitía]

(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("B66").Select
Selection.Delete Shift:=xlUp
Range("B1111").Select
Selection.Delete Shift:=xlUp
Range("B1616").Select
Selection.Delete Shift:=xlUp
Range("D21").Select
End Sub

But which works

 

[keepITcool]

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 >


=?Utf-8?B?SnVhbiBFZHVhcmRvIEdvcmlnb2l0w61h?=

 

[Juan Eduardo Gorigoitía]

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]

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 >


=?Utf-8?B?SnVhbiBFZHVhcmRvIEdvcmlnb2l0w61h?=