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

user_8308768 0
Viewed 1578

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?

excel-2010 is-empty vba excel
2 Answers

I think you're very close, just you have the cells inputs the wrong way around:

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

Additionally as mentioned in the comments you can create a loop to check many columns:

Dim i As Integer
Dim maxi As Integer
i = 1
maxi = 20

While i < maxi

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

i = i + 1
Wend
user_29528240

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.

Admin1
Related