Formula to count how many blank cells are at the end of a range when some other cells can be blank

[tonywatsonhelp]

Hi Everyone,
I'm haveing one of those days lol!
OK so i have arange of AC113:AC137
this range expands when i add data buy adding rows, now what i need to do is know how many empty rows i have at the end of the range?

there can be other blank cells in the range so i cant just count blank and non blanks as i only want the number after the last cell value,
there will always be at least one blank row

 

[Joe4]

Try:

Dim lastRow as Long
lastRow = Cells(Rows.Count,"AC").End(xlUp).Row

This will give you the last row in column AC with data. If you want the row after that, just add 1 to the calculation above.

 

[tonywatsonhelp]

Thanks joe,
its not the last row i want its the number of blank rows

I have a sheets and AC113:AC137 is one of the places that people add data,
I have a great mnacro that add another row to the range each time someone inputs data, but there are time when you dont add directly under the last row
so i need to tell my macro that if the number of blank rows falls below 5 add that number of rows instead of just one, i have the macro all set and woring except i cant work out how to find the the number of blank rows so just a formula to tell me would be fine,

i'm not sure if that helped i might be getting friday night fever! lol

Thanks

Tony

 

[Joe4]

Is that 5 consecutive blank rows?
Or just 5 total blank rows (and if so, across what range exactly)?

Quite frankly, I am a bit confused by the explanation. I think it would be most helpful if we could see an image of what your data actually looks like, to go along with your explanation.

 

[JamesCanale]

Function endblank(r As Range)
    rend = r.Row + r.Rows.Count - 1
    rstart = r.Row
    col = r.Column
    numblanks = 0
    For t = rend To rstart Step -1
        If Cells(t, col) <> "" Then Exit For
        numblanks = numblanks + 1
    Next t
    endblank = numblanks
End Function

This only works on a range that is one column and some number of rows.