Get non-empty cells count and index for some column in 3 worksheets?

**nickxtsui** · 11-22-2013, 07:41 PM

Hi Folks:
How should I do this:
Coding in ThisWorkbook to count how many non-empty cells, as well as the index/position of the non-empty cells, for column D in worksheet1, workshet2, and worksheet3? Returning three integers, N1, N2, and N3 as count of non-empty, and another three integer array I1, I2, and I3, as index array;

For example:

Sheet1 collumn D

1
empty
2
empty
1
empty
2
3
1

The function should return N1 = 6, and I1 = [1 3 5 7 8 9] (assuming 1 based indexing); Thanks a lot!

**AlphaFrog** · 11-22-2013, 08:57 PM

Try something like this.

The three sheet counts are in the N array; N(1), N(2), N(3)
The three row arrays are in the I array (an array of arrays); I(1), I(2), I(3)

    Dim N(1 To 3) As Long, I(1 To 3) As Variant, ii As Variant
    Dim j As Long, k As Long, ws As Worksheet, cell As Range
    
    For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
        With ws.Range("D1", ws.Range("D" & Rows.Count).End(xlUp))
            j = j + 1
            k = 0
            ReDim ii(1 To .Count)
            For Each cell In .Cells
                If cell.Value <> "" Then
                    k = k + 1
                    ii(k) = cell.Row
                End If
            Next cell
            N(j) = k
            ReDim Preserve ii(1 To k)
            I(j) = ii
        End With
    Next ws