Next empty row within range

sharky12345

Well-known Member
Joined
Aug 5, 2010
Messages
3,340
Office Version
  1. 2016
Platform
  1. Windows
I'm using this to enter data in the next empty row;

Code:
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
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

Well-known Member
Joined
May 17, 2009
Messages
5,069
Office Version
  1. 2013
Platform
  1. Windows
sharky12345,

Try...

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

Hope that helps.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,720
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Code:
  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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,720
Office Version
  1. 2010
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top