Macro to delete specific data in tables

[Juan Eduardo Gorigoitía]

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

 

[JulieD]

Hi Juan

there's probably a neater way but this code should work
---
Sub deletegrouptotal()
On Error GoTo err
Range("A1").Select
Do Until i = 1000
Cells.Find(What:="Group Total", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 0).Rows("1:3").EntireRow.Select
Selection.Delete Shift:=xlUp
i = i + 1
Loop
err:
End Sub
---

i = 1000 means look for 1000 occurances of "Group Total" .. you can change
this number to suit your data.

Please test it on a copy of your data first.

Cheers
JulieD



"Juan Eduardo Gorigoitía" <Juan Eduardo
Gorigoití[email protected]> wrote in message
news:[email protected]...