Run-time Error 40036 on ActiveSheet
 

I get a Run-time error 40036 after the .Rows(STARTROW).Insert in this code


Public Sub HideBlanksAndZeros2()
Const MYCOL As String = "J"
Const STARTROW As Long = 8
Dim rng As Range

Application.ScreenUpdating = False
With ActiveSheet
.Rows(STARTROW).Insert
.UsedRange
With Intersect(.Cells(STARTROW, MYCOL).Resize(Rows.count - _
STARTROW), .UsedRange)
.AutoFilter Field:=1, Criteria1:="=", _
Operator:=xlOr, Criteria2:="=0"
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter Field:=1
End With
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveSheet.Rows(STARTROW).Delete
Application.ScreenUpdating = True
End Sub



Any clues as to why?

Run-time Error 40036 on ActiveSheet
 

Hi Brent,

I was able to run the relevant portion of code without problem.Despite
trying everything I could think of that might cause a problem, I was unable
to replicate your error.

Sorry for my unhelpful reponse.

---
Regards,
Norman



"Brent Fidler" wrote in message
om...
I get a Run-time error 40036 after the .Rows(STARTROW).Insert in this code


Public Sub HideBlanksAndZeros2()
Const MYCOL As String = "J"
Const STARTROW As Long = 8
Dim rng As Range

Application.ScreenUpdating = False
With ActiveSheet
.Rows(STARTROW).Insert
.UsedRange
With Intersect(.Cells(STARTROW, MYCOL).Resize(Rows.count - _
STARTROW), .UsedRange)
.AutoFilter Field:=1, Criteria1:="=", _
Operator:=xlOr, Criteria2:="=0"
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter Field:=1
End With
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveSheet.Rows(STARTROW).Delete
Application.ScreenUpdating = True
End Sub



Any clues as to why?

Run-time Error 40036 on ActiveSheet
 

Could not duplicate in Excel 2003

"Brent Fidler" wrote:

I get a Run-time error 40036 after the .Rows(STARTROW).Insert in this code


Public Sub HideBlanksAndZeros2()
Const MYCOL As String = "J"
Const STARTROW As Long = 8
Dim rng As Range

Application.ScreenUpdating = False
With ActiveSheet
.Rows(STARTROW).Insert
.UsedRange
With Intersect(.Cells(STARTROW, MYCOL).Resize(Rows.count - _
STARTROW), .UsedRange)
.AutoFilter Field:=1, Criteria1:="=", _
Operator:=xlOr, Criteria2:="=0"
On Error Resume Next
Set rng = .SpecialCells(xlCellTypeVisible)
On Error GoTo 0
.AutoFilter Field:=1
End With
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
ActiveSheet.Rows(STARTROW).Delete
Application.ScreenUpdating = True
End Sub



Any clues as to why?