Big Idea lacking enough knowledge

[robs_drunk]

Hi
I want to be able to count the rows that have data on one sheet
"S1" (A2:BX2) and use that amount to enter a formula on another sheet
"S2" in starting form A6 and then remove the rows that the formula
equals "0". Any ideas

 

[Dan R.]

Try something like this:

Sub test()
Set rng1 = Sheets("S1").Range("A2:BX2")
For Each cell In rng1
If cell.Value > 0 Then
count = count + 1
End If
Next cell
Set rng2 = Sheets("S2").Range("A6:A100")
rng2.Formula = "=countif($B:$B, " & count & ")"
End Sub

 

[robs_drunk]

Try something like this:

Sub test()
Set rng1 = Sheets("S1").Range("A2:BX2")
For Each cell In rng1
If cell.Value > 0 Then
count = count + 1
End If
Next cell
Set rng2 = Sheets("S2").Range("A6:A100")
rng2.Formula = "=countif($B:$B, " & count & ")"
End Sub

Thanks Dan Works a Treat!!!
How can i get the lines that return "0" now from the formula to
disappear ?