Hi
It’s no good, I just can’t seem to get this right so need to ask for some help.
I need to find a range of blank cells within a fixed range of rows and columns.
So I’m trying to find the first (xlUp) or last (xlDown) row of blank cells across columns P1:S1
and between Rows 6 to 600, so P1:S600. What ever combination I’ve tried has failed!!
Can any one suggest how to do this?
Create a helper column. In the helper column, use COUNTBLANK to count the cells in columns P:S for each row. Then Filter on the helper column for value 4 to give you all rows with 4 blank cells.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
range in this case as you meant (P1:S600)
to find start cell before nonblank cell try this formula (array formula)
{=ADDRESS(MIN(IF(range<>"";ROW(range)))-1;MIN(IF(range<>"";COLUMN(range))))}
to find last cell try use this one (array formula)
{=ADDRESS(MAX(IF(range<>"";ROW(range)))+1;MAX(IF(range<>"";COLUMN(range))))}
try this.
Formula:
Please Login or Register to view this content.
or
Formula:Please Login or Register to view this content.
Last edited by avk; 02-04-2017 at 08:02 PM. Reason: Posting Problem
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
It is to find position of last blank row of cells in a range for the Sub to know where to add the new data.
Like:
Sorry not done a very good job of explaining
Please Login or Register to view this content.
Due to other replies posted this may not be necessary, but will do it any way.
Will give suggestions a go, many thanks
It is to find position of last blank row of cells in a range for the Sub to know where to add the new data.
Like:
Sorry not done a very good job of explainingPlease Login or Register to view this content.
Due to other replies posted this may not be necessary, but will do it any way.
Will give suggestions a go, many thanks
Sorry about double post!!
Last edited by julhs; 02-04-2017 at 08:09 PM.
Suggest you post a sample workbook with some typical data.
What's in the other columns?
Solved on Vba & Macros section
Thread link; http://www.excelforum.com/showthread.php?t=1172925
Last edited by julhs; 02-08-2017 at 01:36 AM. Reason: Link to resolution
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks