Big Idea lacking enough knowledge
 

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

Big Idea lacking enough knowledge
 

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

--
Dan

Big Idea lacking enough knowledge
 

On Jul 18, 12:59 am, "Dan R." wrote:
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

--
Dan

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