delete rows, saving the setting

Posted by amroo on May 10, 2001 1:06 AM

Bonjour,
I have a setted table with 3 columns (Name, Age, Address)and 200 rows .
I want to delete all the rows where the age and address
are blank(empty), and make the followed rows climb up in place of the delete rows without changing the setting (in french cadre).
Is it possible.
A+mroo

Posted by Dave Hawley on May 10, 2001 4:32 AM

Hi Amroo

This code will delete the blank rows in Age and Address if both ar blank. It assumes the Age is column B and Address is column C

Sub DeleteBlanks()
'Written by Ozgrid Business Applications
'www.ozgrid.com

ActiveSheet.AutoFilterMode = False
Range("A1:C1").AutoFilter
Range("A1:C1").AutoFilter Field:=2, Criteria1:="="
Range("A1:C1").AutoFilter Field:=3, Criteria1:="="

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete shift:=xlUp

ActiveSheet.AutoFilterMode = False

End Sub

Dave

OzGrid Business Applications

Posted by amroo on May 10, 2001 6:46 AM

Re: delete rows

Thanks to Dave. I have something else to add. My table is in a frame or a setting and I don' t want it to be reduce 'cause the delete rows.
Is it possible to fixe it.
2001 best continuation
A+mroo