Can I use IsEmpty to refer to a different sheet and hide a column?

Question

Is it possible to use IsEmpty to refer to a cell on a different sheet from where the macro is being fired from? Also, is it possible to hide the queried column if the result of that query is True?

Here's what I've built so far: My first version looked like this:

If IsEmpty(L1) Then
    Columns("L").EntireColumn.Hidden = True
Else
    Columns("L").EntireColumn.Hidden = False
End If

Straightforward enough. But, that only works if it's fired from the worksheet where I want the query/hide to occur. When I launch the macro from the different sheet, it hides the column in that sheet (of course, duh).

So, after several iterations and errors, I got to this:

If IsEmpty(Sheets("Results").Cells(10, 1).Value) Then
    Worksheets("Results").Columns(10).EntireColumn.Hidden = True
Else
    Worksheets("Results").Columns(10).EntireColumn.Hidden = False
End If

Which at least doesn't throw any errors from the VBA. It also does a grand total of squat. :$ I'm starting to wonder if it's even possible to use IsEmpty on a different sheet? Or the EntireColumn.Hidden command? Also, given that I need to run this check on 9 columns, maybe there's a better way than 9 If/Then statements?

Answer 1

To get away from a loop through 9 columns' row 1, use SpecialCells(xlCellTypeBlanks).

dim blnks as range
with workSheets("Results")
    with .range(.cells(1, "B"), .cells(1, "K"))
        .entirecolumn.hidden = false
        set blnks = .specialcells(xlCellTypeBlanks)
        if not blnks is nothing then blnks.entirecolumn.hidden = true
    end with
end with

Essentially this unhides all 9 columns then hides the columns with blank cells in the first row. Note that a zero-length string (e.g. "") returned by a formula is not the same thing as a truly blank cell.