identify longest range of non-blank contiguous cells in a row

Question

I have an Excel spreadsheet of velocity values (see link below) with some blanks interspersed. I am trying to loop through each row and return the range of the longest set of contiguous non-blank cells in each row. I will ultimately use the range address to perform other functions (i.e., average the values within this range). I have used the code below to count the number of columns in a range before, but haven't figured out how to count only non-blank cells and continue counting in the same row.

ColumnCount = Cells(1, Columns.Count).End(xlToLeft).Column

About the image: The highlighted columns represent depth & ensemble numbers, and the non-highlighted values represent velocity values that I would like to process. This spreadsheet continues for another 2,0000 columns. There's a lot of data!

Thank you! Any help would be much appreciated! Marie

Answer 1

You can use Do Until loop and If statements to traverse the entire row from beginning to the end. Below is the example for one row (doesn't have Excel at the moment, so cannot check). maxLength variable stores maximium found at each iteration. Cells(1, currCol).Value = "" used to check whether the continuous range consists of only 1 cell (otherwise, it would count empty range plus 2 nonempty cells plus 1 more cell).

Dim maxLength as Integer
maxLength = 0
currCol = 1
totalCol = Columns.Count
If Cells(1, currCol).Value = "" Then
    currCol = Cells(1, currCol).End(xlToRight).Column
End If
Do Until currCol = totalCol
    prevCol = currCol
    If Cells(1, prevCol + 1).Value = "" Then
        maxLength = WorkSheetFunction.Max(maxLength, 1)
    Else
        currCol = Cells(1, currCol).End(xlToRight).Column
        maxLength = WorkSheetFunction.Max(maxLength, currCol - prevCol + 1)
    End if
    currCol = Cells(1, currCol).End(xlToRight).Column
Loop