MrExcel.com - Your One Stop for Excel Tips & Solutions

counting delete rows

Posted by amroo on May 10, 2001 11:50 PM

Bonjour, I have a table (3 columns, 200 rows) I want to delete all rows that cells in column (B,C) are both blank, count them and add the same number of lines at the end of the table.
I know that the first part of this question has been treated before by D.H.
Solutions gratefully received.
A+mroo

Posted by Dave Hawley on May 11, 2001 12:51 AM

Hi Amroo

If you need the same number of Rows after the removal of the ones which meet the criteria, you would be better off using:

.EntireRow.Clear
instead of
.EntireRow.Delete

Then have the code finish off with a Sort. Then all blank rows will be pushed to the bottom.


Dave

OzGrid Business Applications

Posted by amroo on May 11, 2001 1:28 AM

Hi Dave and co, thanks it's that I need so what is the code to finish off with a Sort.(I don't understand "finish off" .
A+mroo

Posted by Dave Hawley on May 11, 2001 2:05 AM


Amroo, try this:

Sub SortOutBlanks()
'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.Clear

ActiveSheet.AutoFilterMode = False

Range("A2:C500").Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Header:=xlNo, Orientation:=xlSortColumns

End Sub

Dave


OzGrid Business Applications

Posted by amroo on May 11, 2001 5:53 AM

Bon aprés midi everybody,
I copy an past the Dave's code in a macro and add this followed code to get the table in a setting or frame (well I don't know the exact word of the thing where we put pictures or photos?), and it runs well.
If someone can make it more simple, so I thank him or her in advance.
A+mroo
the code added
Range("A2:C200").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With
End With
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone