LinkBack URL
About LinkBacks
I have a set of spreadsheets and each one has a blank line somewhere in the middle area. I need a formula that I can place at the bottom of each spreadsheet that returns the row number of the blank row in each one. All help is very appreciated!
I have a set of spreadsheets and each one has a blank line somewhere in the middle area. I need a formula that I can place at the bottom of each spreadsheet that returns the row number of the blank row in each one. All help is very appreciated!
Try something like this..
Array Formula - Requires CTRL+SHIFT+ENTER
Formula:
=IFERROR(SMALL(IF($A$1:$A$11="",ROW($A$1:$A$11)),ROW(A1)),"")
Drag it down..
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hello,
In case you want to count the number of blank rows, you can use this formula
You will need to adjust A1:A11 to your real file (If you have 1000 rows for example, it will be A1:A1000)
=COUNTBLANK(A1:A11)
This is just a normal formula. It will count the number of blank cell in a column (If you place it on a column that must have a value, you can easily point out which row is blank). The downside is that if your cell has "" in it, it won't be counted as blank.
Last edited by Lemice; 04-30-2013 at 01:33 AM. Reason: clarification
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
Double Posted
Last edited by :) Sixthsense :); 04-30-2013 at 01:43 AM.
Double posted.
Last edited by Lemice; 04-30-2013 at 02:49 AM.
I need the blank rows number. I do not need to count the blank row, I know there is only one; I just need to know its location.
If your date set A1:A100, have some blank cell in A1:A100.
This formula return the blank rows number
Finish by: Ctrl + Shift + Enter (not enter) and Fill down=SMALL(IF($A$1:$A$100="",ROW(INDIRECT("1:"&ROWS($A$1:$A$100))),""),ROW(1:1))
This formula return the end of the blank row number in A1:A100
Hope to help you!=LOOKUP(2,1/($A$1:$A$100=""),ROW(INDIRECT("1:"&ROWS($A$1:$A$100))))
This worked! thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks