Delete rows with zeroes

**maxthebear** · 03-13-2009, 11:40 AM

Attached is a file showing two groups. The first group shows the results of ten tests. I am trying to find a macro or function that will show only those tests that have a result of at least 1 (the second group in the file).

Filtering is no good as the users won't be able to manipulate the data. I appreciate any help you guys can offer.......

Max

**JBeaucaire** · 03-13-2009, 02:05 PM

Not sure what you're going to do next, and sometimes "examples" like the one you posted are hard to implement into your real situation, but this is one way to filter small sets of data like that.

On your sheet, in C18, enter this formula and press CTRL-SHIFT-ENTER to activate an array. You'll see braces { } appear around your formula and "Test 1" will appear. If you get an error, press F2 and CTRL-SHIFT-ENTER to try again.

=IF(ROWS($C$19:C19)>COUNTIF($D$4:$D$13,">0"),"",INDEX($C$4:$C$13,SMALL(IF($D$4:$D$13>0,ROW($A$1:$A$10),""),ROWS($C$19:C19))))

Then in D18 this formula for the score:

=IF(C18="","",INDEX($D$4:$D$13,MATCH(C18,$C$4:$C$13,0)))

Now copy those two cells down as far as you need.

**maxthebear** · 03-13-2009, 02:37 PM

I gave it a quick try and it "kinda" worked. I need to sit down and go through it to make sure. In the meantime, thanks...........

Max

**JBeaucaire** · 03-13-2009, 02:48 PM

If you have a real-world document you need help applying those formulas to, let me know. The arrays are a little tricky, but easy once you get used to them and pressing CSE.

**bill2073** · 03-13-2009, 04:57 PM

See message below.

**bill2073** · 03-13-2009, 06:18 PM

Give this macro a try...Bill

Sub find()
'
' find Macro
' Macro recorded 3/13/2009 by Microchip
'
Range("A1").Select

Cells.find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

For counter = 1 To 10

If ActiveCell = 0 Then
Range(ActiveCell, ActiveCell.Offset(0, -3)).Delete
End If

Cells.FindNext(After:=ActiveCell).Activate
On Error Resume Next

Next counter

End Sub