VBA code need to account for empty rows

Euro5 used Ask the Experts™
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
Watch Question

Do more with

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

What's the error?

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


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

Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
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