Find consecutive non blank cells in a column

Hello , Maybe this is a simple questions but I am stumped at it.

I have a column that has some cells populated with numbers but majority of cells are blank in that row.

What I want to find is if there are any non blank cells that are next to each other? How can i find two consecutive non-blank cells within a range say for example (A1:A200) in the column? Can someone please give me some pointers?

Hi,

When you say you want to 'find' them, exactly what do you wish to have returned? The row numbers of each of these cells?

What if there is more than one occurrence of a range of non-blank cells in your range? Do you want those returned as well, or just the first?

Where do you want them returned?

Regards

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

Hi,

Perhaps this array formula in C2 and copy down, though it will require some testing:

=IF(A2="","",IF(COUNTBLANK($B3:INDEX($B3:$B$1000,MATCH(TRUE,INDEX($A3:$A$1000<>"",,),0)-1))=0,"Incomplete",IF(SUMPRODUCT(--(FREQUENCY(IF($B3:INDEX($B3:$B$1000,MATCH(TRUE,INDEX($A3:$A$1000<>"",,),0)-1)<>"",ROW($B3:INDEX($B3:$B$1000,MATCH(TRUE,INDEX($A3:$A$1000<>"",,),0)-1))),IF($B3:INDEX($B3:$B$1000,MATCH(TRUE,INDEX($A3:$A$1000<>"",,),0)-1)="",ROW($B3:INDEX($B3:$B$1000,MATCH(TRUE,INDEX($A3:$A$1000<>"",,),0)-1))))>1))>0,"Partially Complete","Complete")))

You will also need to ensure that there is a final entry in column A to use as a 'marker' for the range to be considered for the final person (e.g. in your example, cell A40 must be non-blank so that the formula can determine the range for Frank Sinds).

Regards

Hi,

In which cells are you getting the error? Are you testing on a sheet identical to the one you attached?

Regards

Ok, so if we're going to have a potential case where the last row in column A may not be populated, can we have some sort of upper bound on the number of cells to set for the range? I mean, we need to make sure we include all necessary data for e.g. Frank Sinds, so how else can we determine this?

Perhaps we could say, for example, that the range of cells to be considered for any one person will never be greater than 50 rows? Or some other upper limit?

Or do you have any other indicator(s) in mind? What would you expect a macro to look for that a worksheet formula could not?

Regards

I'm sure it does, but I'm afraid I won't be able to help you with a VBA solution. Hopefully someone with more expertise in that area will pick up on this thread shortly. In the meantime, I'll ask for some support.

Regards

Ok, but surely you're in a position to name a suitable upper limit, aren't you? I mean, you say it could be 500, so can't we say, e.g. 5000 and be 99.999% certain that this will cover us? Do you see what I'm saying?

Regards

Yes yes, the upper limit can be set to 500, it will cover 99.999 % of cases. And I can also populate the column A with Names as a part of my macro to make sure that column A always has values and so they are not blank but have the Names, if this will help.

Edit:
Just attached a file with the column A populated, as to what I meant.

Hi Desi,

I'll try my hand tomorrow if you still need it.

Hi desibabuji

This Code is in the attached and appears to work on your Sample File. Run the Code from the Button on Sheet BEFORE (2).

Please Login or Register  to view this content.

Hi jaslake.

Thanks so much for the code. Will it be difficult to alter it to work with the condition when column A is populated with names, as in the BEFORE sheet attached? I mean the code works but then while I was working on the solution, I realized that if the column A is fully populated, I could also accommodate another macro with the new format

What I mean is that instead of looking for "blank cells" in the column, can the code be modified to insert the column C values when the Name in column A changes, eg from John to Mario or Frank , (row 2, 6 and 22)!

If it is too much of effort then please let me know, I can go ahead and revert my approach to what I originally had, this is more of a nice to have. Thanks a lot for your time and effort. I really do appreciate it.

Hi desibabuji

I'd be happy to look at the revision but I need to tell you, I'm not at all convinced that the Code provided will work on your Live File. There's too much logic involved.

I'd like you to provide more Test Data...perhaps a dozen items or so with differing scenarios. Perhaps 4 to 5 of each, interspersed.

Present the new Sample Data in the Format you desire...Expected Results would be nice...we'll see what we can do.

Hi desibabuji

Please do this

I'd like you to provide more Test Data...perhaps a dozen items or so with differing scenarios. Perhaps 4 to 5 of each, interspersed.

Present the new Sample Data in the Format you desire...Expected Results would be nice...we'll see what we can do.

Hi desibabuji

With your new Format it'll involve a rather significant rewrite of the Code. I'll look at it this afternoon as time permits.

Hi desibabuji

Try yudlugar's Code...let us know.