Next empty row within range

[sharky12345]

I'm using this to enter data in the next empty row;

iRow = ws.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row

Can anyone show me how to adapt it so that it finds the next empty row within a range, i.e. between A26:A56?

 

[NeonRedSharpie]

iRow = ws.Cells(startRow, 1).End(xlDown).Row + 1

But you'll need some kind of a condition to not look past row 56. Such as a case statement and if iRow is larger than 56, return "No empty rows found" or something.

 

[Snakehips]

sharky12345,

Try...

iRow = Application.WorksheetFunction.Max(Cells(57, 1).End(xlUp).Row + 1, 26)

Hope that helps.

 

[sharky12345]

Thanks guys!!!

 

[Rick Rothstein]

Here is another code snippet for you to consider...

  If Len(Range("A56").Value) Then
    '
    ' Last cell in range is filled...
    ' not sure what you want to do in that,
    ' case, but here is where you would do it.
    '
  Else
    iRow = Application.Max(26, Cells(56, "A").End(xlUp).Row + 1)
  End If

 

[sharky12345]

Thank you Rick!

 

[Rick Rothstein]

Thank you Rick!

I think you might have been too quick in reading my message... read it again as I made a minor change in the code which you may have missed (to make sure it didn't return a cell earlier than A26 if no cells were filled in the range A26:A56).

 

[MarkB1]

Not to hijack the thread, but I'm having issues accomplishing the same within Excel 2013 using a specific data range: http://www.mrexcel.com/forum/excel-...13-unable-detect-blank-cells.html#post3956898

Any ideas? Thanks!