Check if there is empty Range in vba

Question

I am trying to filter by a list of Condition from the Condition wb to use for the Order wb. I use a checkEmpty range in order to check if there are no matching value then I will clear the filter and start with the next condition. But my code doesn't work and the error is "Range of object_worksheet" failed. I get the error because even there is no matching value (empty range), the code still jump to Else condition. Here is my code:

Sub Order()

Dim start As Double
Dim strKeyWord As String
Dim myCount As Integer
Dim checkEmpty As Range
Dim lRow1 As Long

Dim wsOrder As Worksheet
Dim wsCondition As Worksheet
Dim wbOrder As Workbook
Dim wbCondition As Workbook

Dim OrderFile As String
Dim ConditionFile As String

'Open Order wb
OrderFile = Application.GetOpenFilename()
Set wbOrder = Workbooks.Open(OrderFile)
Set wsOrder = wbOrder.Worksheets(1)

'Open Condition wb
ConditionFile = Application.GetOpenFilename()
Set wbCondition = Workbooks.Open(ConditionFile)
Set wsCondition = wbCondition.Worksheets(1)

'using the CountA ws function (all non-blanks)
myCount = Application.CountA(wsCondition.Range("A:A")) - 1

start = 2

For I = 1 To myCount Step 1

    strKeyWord = wsCondition.Range("A" & start)
    wsOrder.Range("R:R").AutoFilter Field:=1, Criteria1:="=*" & strKeyWord & "*"

    'lRow1 = WorksheetFunction.Max(wsOrder.Range("I65536").End(xlUp).Row)
    Set checkEmpty = wsOrder.Range("I2:I100").SpecialCells(xlCellTypeVisible)

    If checkEmpty Is Nothing Then
        On Error Resume Next
        wsOrder.ShowAllData
        On Error GoTo 0
    Else
        wsOrder.Range("I2", Range("I" & Rows.Count).End(xlUp)).Copy
        With wsCondition
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial
        End With
    End If
    start = start + 1

Next I
End Sub

Thank you very much!

Hello. I am sorry for unclear explanation. I got the error because the code jump to the else condition even after filter there no value (empty cell). — Huỳnh Tùng
– Huỳnh Tùng, Commented Aug 28, 2018 at 12:34
You need two tests. You need a test that your specialcells applies i.e that there are visible cells in the range, you then also need a test in case no match is found of If not Is Nothing checkEmpty Then.... You appear to have implemented the second — QHarr
– QHarr, Commented Aug 28, 2018 at 12:34
Also you use a For I loop but you never use I instead you count a separate start variable. I think you should use something like For start = 2 To myCount Step 1 … Next start and omit the start = 2 and start = start + 1 lines. — Pᴇʜ
– Pᴇʜ, Commented Aug 28, 2018 at 12:44
And you should specify a worksheet for the second range in wsOrder.Range("I2", Range("I" & Rows.Count) like wsOrder.Range("I2", wsOrder.Range("I" & Rows.Count) — Pᴇʜ
– Pᴇʜ, Commented Aug 28, 2018 at 12:52

Pᴇʜ · Accepted Answer · 2018-08-28 13:14:22Z

So the main issue is that you didn't specify a worksheet for Range("I" & Rows.Count).End(xlUp).

Using

wsOrder.Range("I2", Range("I" & wsOrder.Rows.Count).End(xlUp)).Copy

should fix that.

But also I would correct the For I loop because you never use I. But you don't need the start variable and can use I instead which is also auto incremented.

'using the CountA ws function (all non-blanks)
myCount = Application.CountA(wsCondition.Range("A:A")) 'removed the -1

'remove start=2 and replace start with I

For I = 2 To myCount Step 1
    strKeyWord = wsCondition.Range("A" & I)
    wsOrder.Range("R:R").AutoFilter Field:=1, Criteria1:="=*" & strKeyWord & "*"

    'lRow1 = WorksheetFunction.Max(wsOrder.Range("I65536").End(xlUp).Row)
    Set checkEmpty = wsOrder.Range("I2:I100").SpecialCells(xlCellTypeVisible)

    If checkEmpty Is Nothing Then
        On Error Resume Next
        wsOrder.ShowAllData
        On Error GoTo 0
    Else
        wsOrder.Range("I2", Range("I" & Rows.Count).End(xlUp)).Copy
        With wsCondition
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial
        End With
    End If   
Next I

Collectives™ on Stack Overflow

Check if there is empty Range in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related