VBA code need to account for empty rows

Euro5
Euro5 used Ask the Experts™
on
The code looks at three sheets and runs code.
But there is a possibility that there is nothing on one of the three rows.
This resulted in an error - can I add a handler?

Sub GetSCS(strSheet As String, varCol As Variant)

Error on                 intLowWt = varDataS(lngRowS, 2)

in this circumstance, I had no data in sheet Intl
What-if-scenario-v4.3.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
What's the error?

How can we recreate it? ie which sub should be run or button press

Author

Commented:
the sub is Sub GetSCSDriver() which calls the
Sub GetSCS(strSheet As String, varCol As Variant)

You could use the button, but probably more expedient just to run the
Sub GetSCSDriver

THANKS!!
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Add line 23 (I didn't show all the sub)..
Sub GetSCS(strSheet As String, varCol As Variant)
Dim lngLastRowS As Long
Dim lngLastRowD As Long
Dim lngRowS As Long
Dim lngRowD As Long
Dim lngE As Long
Dim strParts() As String
Dim intLowWt As Integer
Dim intHighWt As Integer
Dim varDataS As Variant
Dim varDataD As Variant
Dim MyVar As String
Dim rgBD As Variant
Dim rgBM As Range
Application.Calculation = xlCalculationManual
lngLastRowD = Sheets("Detail").UsedRange.Rows.Count

lngLastRowS = Sheets(strSheet).Range("A80000").End(xlUp).Row
' Note that these are set up to be the VALUES in the sheet rather than
' this which would refer to the sheet itself
'    Set varDataS = Sheets("Summary").Range("A3:E" & lngLastRowS)
varDataS = Sheets(strSheet).Range("A3:AQ" & lngLastRowS).Value
If UBound(varDataS) = 3 Then Exit Sub

varDataD = Sheets("Detail").Range("A2:BI" & lngLastRowD).Value

With Sheets("Detail")
    Set rgBM = Range(.Cells(2, varCol), .Cells(.Rows.Count, varCol).End(xlUp))
    rgBM.Offset(1, 0).ClearContents
End With
...

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial