I have several subroutines that accept a worksheet as a parameter. When I pass the parameter using the sheet name (i.e., sht1, shtSetup) the code is inconsistent. Specifically, the first time I run the code it works fine. If I run it again immediately after I get a runtime 91 - Objective variable not set error. When I stop the code and try it again it works fine. Additionally, after running the code several times Excel often crashes. However, if I pass the worksheet parameter using Worksheets("1-Sources") and Worksheets("Setup") there are no issues.
Below is the code inn question. The first subroutine in on the sheet and the second is in a module.
Any ideas why this is not working consistently?
Below is the code inn question. The first subroutine in on the sheet and the second is in a module.
Any ideas why this is not working consistently?
VBA Code:
Sub cmdHideUnusedSources()
HideSheetRowsSpecificCellLessThan sht1, shtSetup.Range("Setup1aStart"), _
GetColumnNumber(shtSetup.Range("Setup1CheckColumn")), shtSetup.Range("Setup1CheckAmount")
End Sub
Sub HideSheetRowsSpecificCellLessThan(sSheet As Worksheet, StartRange As Range, CheckColumn As Integer, CheckAmount As Double) 'NEW
Dim C As Integer
Dim I As Integer
Application.ScreenUpdating = False
Application.Calculation = xlManual
C = 1
Do Until StartRange.Offset(C, 0) = ""
For I = StartRange.Offset(C, cEndRow) To StartRange.Offset(C, cStartRow) Step -1
If sSheet.Cells(I, CheckColumn) < CheckAmount Then
sSheet.Cells(I, 1).EntireRow.Hidden = True
End If
Next I
C = C + 1
Loop
CleanUp:
Set sSheet = Nothing
Set StartRange = Nothing
Application.Calculation = xlAutomatic
End Sub