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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,860
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
Try:
VBA Code:
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

Well-known Member
Joined
Feb 24, 2014
Messages
2,860
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,481
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jan 13, 2021
Messages
327
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
VBA Code:
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.
 
Solution

Forum statistics

Threads
1,144,163
Messages
5,722,853
Members
422,461
Latest member
kelleys315

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