When using Arrays in Excel VBA you sometimes need to test whether the array is empty or not.
Most of the time you could simply test to see if an array is populated by checking the Lbound of the array. When testing, if you get an error then the array is empty. Whereas no error suggests the array is not empty.
So, to test the Lbound you could use the following the VBA code.
ThisArray would simply be replaced with your array name.
Dim ThisArray() As Long
Dim Nmbr As Long
On Error Resume Next
Nmbr = LBound(ThisArray)
If Err.Number = 0 Then
'Do this... (array is NOT empty)
'Do this... (array IS empty)
I hope you find this snippet of VBA code useful.