我很需要帮助来确定我的Excel VBA有什么问题。我是一个彻头彻尾的初学者。
基本上,这个VBA代码将为工作表执行“筛选”。奇怪的是,当筛选其他类别时,VBA可以正常运行。但对于一类别,它会不断给出运行时错误1004(对象_Worksheet的方法Range失败)。
调试模式总是指向以下代码行:
Range(Mid(rangeToHide, 1, 199)).Select
这是代码:
这是代码:
Private Sub cboPopulateDept_Change()
Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer
Dim rangeToHide As String
Dim emptyRow As Integer
unHide
If cboPopulateDept.Value = "ALL" Or cboPopulateDept.Value = "" Then
Exit Sub
End If
RowCount = 1
Set sh = ActiveSheet
For Each rw In sh.Rows
If RowCount >= 6 Then
If sh.Cells(RowCount, 1).Value Like "TOP Innovation Projects - Vision 2020 - Participating?" Then
Exit For
End If
If sh.Cells(RowCount, 3).Value <> cboPopulateDept.Value And sh.Cells(RowCount, 3).Value <> "" Then
'sh.Cells(RowCount, 3).EntireRow.Hidden = True
'sh.Cells(RowCount + 1, 3).EntireRow.Hidden = True
rangeToHide = rangeToHide & RowCount & ":" & RowCount + 1 & ","
RowCount = RowCount + 2
Else
RowCount = RowCount + 1
End If
Else
RowCount = RowCount + 1
End If
Next rw
rangeToHide = Mid(rangeToHide, 1, Len(rangeToHide) - 1)
If Len(rangeToHide) <= 201 Then
Range(rangeToHide).Select
Selection.EntireRow.Hidden = True
Else
Range(Mid(rangeToHide, 1, 199)).Select
Selection.EntireRow.Hidden = True
Range(Mid(rangeToHide, 201, Len(rangeToHide))).Select
Selection.EntireRow.Hidden = True
End If
'Range(rangeToHide).Select
'Selection.EntireRow.Hidden = True
Range("A8:A9").Select
End Sub
谢谢
祝好, RH