VBA 统计所有sheet每列的空值率

Sub Null_Rate()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim line As Integer
Dim col As Integer
Dim line_null As Integer
Dim line_max As Integer

Set xlBook = ThisWorkbook

For x = 1 To xlBook.Sheets.Count

col = 1
line = 1

Set xlSheet = xlBook.Sheets(x)
If col = 1 Then
Do
line = line + 1
Loop Until Len(xlSheet.Cells(line, col).Value) = 0
line_max = line
xlSheet.Cells(line, col).Value = Format(0, "Percent")
col = col + 1
End If

Do

If col <> 1 Then
line_null = 0
line = 1
Do

If Len(xlSheet.Cells(line, col).Value) = 0 Then
line_null = line_null + 1
End If
line = line + 1
Loop Until line_max = line

xlSheet.Cells(line, col).Value = Format(line_null / (line_max - 1), "Percent")
End If
col = col + 1
Loop Until Len(xlSheet.Cells(1, col - 2).Value) = 0

If col <> 1 Then
line_null = 0
line = 1
col = col + 1
Do

If Len(xlSheet.Cells(line, col - 1).Value) = 0 Then
line_null = line_null + 1
End If
line = line + 1
Loop Until line_max = line

xlSheet.Cells(line, col - 1).Value = Format(line_null / (line_max - 1), "Percent")
End If

Next x

 

End Sub

 

转载于:https://www.cnblogs.com/sundy818/p/10120339.html