Next Emptycell row #
 

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

Next Emptycell row #
 

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($A 1))

Entered with Ctrl+Shift+Enter

HTH

"dan" wrote:

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

Next Emptycell row #
 

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" wrote in message
...
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($A 1))

Entered with Ctrl+Shift+Enter

HTH

"dan" wrote:

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

Next Emptycell row #
 

Dan,

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

"dan" wrote:

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" wrote in message
...
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($A 1))

Entered with Ctrl+Shift+Enter

HTH

"dan" wrote:

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

Next Emptycell row #
 

This is it!
Thanks a lot.

"Toppers" wrote in message
...
Dan,

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

"dan" wrote:

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" wrote in message
...
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($A 1))

Entered with Ctrl+Shift+Enter

HTH

"dan" wrote:

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

Next Emptycell row #
 

Glad it's OK and thanks for the feedback.

"dan" wrote:

This is it!
Thanks a lot.

"Toppers" wrote in message
...
Dan,

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

"dan" wrote:

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" wrote in message
...
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($A 1))

Entered with Ctrl+Shift+Enter

HTH

"dan" wrote:

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