Next Emptycell row #

[dan]

Is there a simplified vba code for the following routine?

Sub test()
Dim row, NextRow As Long
For row = 6 To 20
If IsEmpty(Cells(row, "C")) Then Cells(1, "C") = row: Exit Sub
Next row
End Sub

And a formula if possible. Thanks

 

[Toppers]

If there are no empty rows interspered in the data

=CountA($C$6:$C$20)+6 will give next empty row

or if there are embedded blanks

=SMALL(IF($C$6:$C$20="",ROW($A$6:$A$20),""),ROW($A1))

Entered with Ctrl+Shift+Enter

HTH

 

[dan]

Thank you, Toppers
My data are interspersed and contain blank cells which are not empty (cells
contains apostrophe).
The array formula "=SMALL(IF($C$6:$C$20="",ROW($A$6:$A$20),""),ROW($A1))"
works good for blanks.
Do you have one for empty cells.

 

[Toppers]

Dan,

Try:
=SMALL(IF(ISBLANK($C$6:$C$20),ROW($A$6:$A$20),""),ROW($A1))

 

[dan]

This is it!
Thanks a lot.

Dan,

Try:
=SMALL(IF(ISBLANK($C$6:$C$20),ROW($A$6:$A$20),""),ROW($A1))

 

[Toppers]

Glad it's OK and thanks for the feedback.

This is it!
Thanks a lot.