Next Emptycell row #

  • Thread starter Thread starter dan
  • Start date Start date
D

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
 
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
 
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying data to the next blank row 0
Write record from Form to new row in spreadsheet 1
Select first empty row in a column 9
expand duplicate check to multiple columns 3
Excel Worksheet_change with 2 functions to insert date and hyperlink window 0
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Excel 2003 VBA problem 4
Data Entry Form: Complex Example 4

Back
Top