Evaluating multiple cells in a range

[armesca]

I have code in access vba to export data to excel. If certain cells are blank in each worksheet, I want the sheet hidden in excel. With my code below, it only appears to be evaluating the first cell in my range. Any thoughts?

For Each objws In objwb.Worksheets
objws.Activate
ActiveSheet.Range("A5,A18,A49,A64,A77,A115,A130,A143,A177,A192,A205,A249").Select
MsgBox Selection
If IsEmpty(Selection) Then
objws.Visible = False
End If
Next objws

 

[boblarson]

You can't do it that way. This should work though.

    Dim objXL  As Object
    Dim objWb  As Object
    Dim objWS  As Object
    Dim iWsCount As Integer
    Dim iWsRangeCount As Integer
 
    Dim varSplit As Variant
 
    varSplit = Split("A5,A18,A49,A64,A77,A115,A130,A1 43,A177,A192,A205,A249", ",")
 
    Set objXL = CreateObject("Excel.Application")
 
    Set objWb = objXL.Workbooks.Open("FilePathAndNameHere")
 
    For iWsCount = 1 To objWb.Worksheets.Count
        For iWsRangeCount = 0 To UBound(varSplit)
            If IsEmpty(objWS.Range(varSplit(iWsRangeCount))) Then
                objWS.Visible = False
                Exit For
            End If
        Next
    Next

Now this is assuming you want the sheet hidden if ANY of the specific cells are empty. But if they ALL need to be empty then you would use this:

    Dim objXL  As Object
    Dim objWb  As Object
    Dim objWS  As Object
    Dim iWsCount As Integer
    Dim iWsRangeCount As Integer
    Dim blnAllEmpty As Boolean
 
    Dim varSplit As Variant
 
    varSplit = Split("A5,A18,A49,A64,A77,A115,A130,A1 43,A177,A192,A205,A249", ",")
 
    Set objXL = CreateObject("Excel.Application")
 
    Set objWb = objXL.Workbooks.Open("FilePathAndNameHere")
 
    For iWsCount = 1 To objWb.Worksheets.Count
        For iWsRangeCount = 0 To UBound(varSplit)
            If IsEmpty(objWS.Range(varSplit(iWsRangeCount))) Then
                blnAllEmpty = True
            Else
                blnAllEmpty = False
                Exit For
            End If
        Next
        If blnAllEmpty Then
            objWS.Visible = False
        End If
    Next

 

[armesca]

Thanks bob, I will give that a try.

 

[armesca]

bob, I tried the latter code you posted with the boolean and can't get it to work. It appears it is getting stuck in the loop:


varSplit = Split("A5,A18,A49,A64,A77,A115,A130,A143,A177,A192,A205,A249", ",")
For iwsCount = 1 To objwb.Worksheets.Count
For iwsRangeCount = 0 To UBound(varSplit)
If IsEmpty(objws.Range(varSplit(iwsRangeCount))) Then
MsgBox objws.Name
blnallempty = True
Else
blnallempty = False
Exit For
End If
Next
If blnallempty Then
objws.Visible = False
End If
Next

 

[armesca]

THe code isn't getting stuck in the loop, but appears to only evaluate the first worksheet. Any ideas?

 

[boblarson]

Put a breakpoint into the code and then use F8 to move through it to see what is happening. Many times when you see that, it helps you to spot what the problem is.

 

[armesca]

Is there a reason within the section of code if I wanted to delete the sheets instead of hide them, that delete would not work. I have gone line by line and the code passes the delete line, but in the final product, it doesn't work. Any suggestions?

 

[boblarson]

Deleting is going to change the worksheet count and it changes the index of the worksheets so you would miss some if they are deleted. If you want to do that you will have to iterate through the worksheets using

For iwsCount = objwb.Worksheets.Count To 0 Step -1