0

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

mariec123
  • 13
  • 4

1 Answers1

0

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
ENIAC
  • 813
  • 1
  • 8
  • 19
  • Thank you very much! I fixed one typo in WorksheetFunction, but it is also giving me an application-defined or object-defined error for the second to last line "currCol = Cells...". and won't let the code run. Any thoughts on why this error is coming up? – mariec123 Sep 08 '20 at 16:58
  • Can you provide your file or a screenshot of the sheet you're dealing with? – ENIAC Sep 08 '20 at 17:20
  • I've edited my original post to include a link with a screenshot! Hope that helps explain what I'm trying to do. – mariec123 Sep 08 '20 at 18:10
  • It was due to another one typo I made: `currColl` instead of `currCol` was written. Corrected. – ENIAC Sep 08 '20 at 19:21
  • Amazing! I really appreciate your help! – mariec123 Sep 09 '20 at 02:04
  • @mariec123, if the answer helped you solve your problem, then mark it as answer. It will be helpful for others to instantly see that the question has an answer. – ENIAC Sep 09 '20 at 07:17