check for IsEmpty(MyArray) = True
 

I want to check to see if an array is empty or not. The array is a named
range on the worksheet and if there are no entries in that range I want to
GoTo another section of code. I am looping through several range names. What
do I need to do?

Option Explicit
Dim myArray As Variant
Dim card As Integer
Dim port As Integer
Dim arName As String
Dim cntr As Integer
Dim oldStatusBar As Boolean
Dim newHour As Date
Dim newMinute As Date
Dim newSecond As Date
Dim waitTime As Date

Sub Run_All()

card = 1
port = 0
cntr = 3

Application.ScreenUpdating = False
For card = 1 To 13
For port = 0 To 11
If card = 7 Then card = card + 1
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Calculating port " & card & "_" & port
arName = "port" & card & "_" & port
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
If IsEmpty(myArray) = True Then GoTo 1 'This line never goes to 1:
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = card & "-" & port
GetPort
If Range("A13").Value = ("No Modem Data for this report") Then
Range("A13").Cut Destination:=Range("A12")
Filter
Sort
1:
cntr = cntr + 1
Next
Next
Sheet125.Select
Range("I18").Formula = "=COUNTA('1-0:13-11'!A13:A100)"
Application.StatusBar = "DONE!"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
Application.ScreenUpdating = True
End Sub

Mike F

check for IsEmpty(MyArray) = True
 

if Application.CountA(ThisWorkbook.Names(arName).Refe rsToRange) = 0 then
GoTo 1

--
Regards,
Tom Ogilvy

"Mike" wrote in message
...
I want to check to see if an array is empty or not. The array is a named
range on the worksheet and if there are no entries in that range I want to
GoTo another section of code. I am looping through several range names.
What
do I need to do?

Option Explicit
Dim myArray As Variant
Dim card As Integer
Dim port As Integer
Dim arName As String
Dim cntr As Integer
Dim oldStatusBar As Boolean
Dim newHour As Date
Dim newMinute As Date
Dim newSecond As Date
Dim waitTime As Date

Sub Run_All()

card = 1
port = 0
cntr = 3

Application.ScreenUpdating = False
For card = 1 To 13
For port = 0 To 11
If card = 7 Then card = card + 1
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Calculating port " & card & "_" & port
arName = "port" & card & "_" & port
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
If IsEmpty(myArray) = True Then GoTo 1 'This line never goes to 1:
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = card & "-" & port
GetPort
If Range("A13").Value = ("No Modem Data for this report") Then
Range("A13").Cut Destination:=Range("A12")
Filter
Sort
1:
cntr = cntr + 1
Next
Next
Sheet125.Select
Range("I18").Formula = "=COUNTA('1-0:13-11'!A13:A100)"
Application.StatusBar = "DONE!"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
Application.ScreenUpdating = True
End Sub

Mike F

check for IsEmpty(MyArray) = True
 

Thanks Tom,
That works great. It seems once declared, the array is not empty, even
though there is no data in it. So I must count the items in the named range
itself. Thank God it's Friday.
Tom Ogilvy wrote in message
...
if Application.CountA(ThisWorkbook.Names(arName).Refe rsToRange) = 0 then
GoTo 1

--
Regards,
Tom Ogilvy

"Mike" wrote in message
...
I want to check to see if an array is empty or not. The array is a named
range on the worksheet and if there are no entries in that range I want
to
GoTo another section of code. I am looping through several range names.
What
do I need to do?

Option Explicit
Dim myArray As Variant
Dim card As Integer
Dim port As Integer
Dim arName As String
Dim cntr As Integer
Dim oldStatusBar As Boolean
Dim newHour As Date
Dim newMinute As Date
Dim newSecond As Date
Dim waitTime As Date

Sub Run_All()

card = 1
port = 0
cntr = 3

Application.ScreenUpdating = False
For card = 1 To 13
For port = 0 To 11
If card = 7 Then card = card + 1
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = "Calculating port " & card & "_" & port
arName = "port" & card & "_" & port
myArray = ThisWorkbook.Names(arName).RefersToRange.Value
If IsEmpty(myArray) = True Then GoTo 1 'This line never goes to 1:
Worksheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = card & "-" & port
GetPort
If Range("A13").Value = ("No Modem Data for this report") Then
Range("A13").Cut Destination:=Range("A12")
Filter
Sort
1:
cntr = cntr + 1
Next
Next
Sheet125.Select
Range("I18").Formula = "=COUNTA('1-0:13-11'!A13:A100)"
Application.StatusBar = "DONE!"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
Application.ScreenUpdating = True
End Sub

Mike F