ntyyz 发表于 2012-12-24 21:37
还要再请教一下,如何在筛选的时候将数据中空白的单元格去掉,只显示有数值的单元格?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$1" Then
On Error Resume Next
Dim BRR(1 To 500, 1 To 4)
Sheets("自动筛选").Range("A3:T536").ClearContents
Arr = Sheets("数据").Range("A2:H" & Sheets("数据").Range("A65536").End(3).Row)
For I = 1 To 5
For J = 1 To UBound(Arr)
If Target.Value = "全部" Then
If Arr(J, I + 3) > 5.7 Or Arr(J, I + 3) < 5.2
And Arr(J, I + 3) <> "" Then
N = N + 1
For T = 1 To 3
BRR(N, T) = Arr(J, T)
Next
BRR(N, 4) = Arr(J, I + 3)
End If
Else
If Arr(J, 1) = Target.Value And (Arr(J, I + 3) > 5.7 Or Arr(J, I + 3) < 5.2)
And Arr(J, I + 3) <> "" Then
N = N + 1
For T = 1 To 3
BRR(N, T) = Arr(J, T)
Next
BRR(N, 4) = Arr(J, I + 3)
End If
End If
Next
Range("A3").Offset(0, (I - 1) * 4).Resize(N, 4) = BRR
Erase BRR
N = 0
Next
End If
End Sub