Excel VBA计数每个非空单元下面的空白单元数 [英] excel vba count number of blank cells below each non empty cells

问题描述

我对Excel VBA很陌生。我想做的是创建一个VBA循环，该循环将计算每个非空单元格下面的单元格数量。

I am quite new with Excel VBA. What I want to do is create a VBA loop that will count the number of cells below each non empty cells.

col c   col d
abc     1
        2
        3
        4
abc     5
        6
        7
        8
        9
        10

以下是我到目前为止尝试过的内容：

Here's what I've tried so far:

Sub test()

Dim a, b, c, d, i, k As Integer
Dim y As Range

k = Worksheets("Sheet2").Range("d" & Rows.Count).End(xlUp).Row '13
a = 3
b = 3

For i = 4 To k                        
    If IsEmpty(Cells(i, 3)) = True Then        
        c = c + 1                    
    Else        
        d = d + 1            
    End If    
Next

MsgBox c
MsgBox d

End Sub

推荐答案

尝试一下

Sub main2()
    Dim iArea As Long
    Dim rng As Range

    With Worksheets("Sheet2")
        Set rng = .Range("D4", .Cells(.Rows.count, "D").End(xlUp)).Offset(, -1) '<--| set the range of its column "C" cells corresponding to its column "D" ones from row 2 down to last not empty one
        With rng.SpecialCells(xlCellTypeConstants) '<--| reference not empty rng cells
            For iArea = 1 To .Areas.count - 1
                MsgBox .Parent.Range(.Areas(iArea).Cells(1, 1), .Areas(iArea + 1).Cells(1, 1).Offset(-1)).SpecialCells(xlCellTypeBlanks).count
            Next iArea
            MsgBox .Parent.Range(.Areas(iArea).Cells(1, 1), rng(rng.Rows.count)).SpecialCells(xlCellTypeBlanks).count
        End With
    End With
End Sub

这篇关于Excel VBA计数每个非空单元下面的空白单元数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！