public sub distinctcount() '求col列中startrow到endrow范圍中不重復的個數 '本例是計算標題為sheet1的excel表中的a1:a240的不重復值的個數 '修改下面四行的結尾值 dim sheetscaption as string: sheetscaption = "sheet1" dim col as string: col = "a" dim startrow as integer: startrow = 1 dim endrow as integer: endrow = 240 '以下固定 dim count as integer: count = 0 with sheets(sheetscaption)
for i = startrow to endrow count = count + 1 for j = startrow to i - 1 if .range(col & i) = .range(col & j) then count = count - 1 exit for end if next next end with msgbox count end sub
