3

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?

rdplanglois
  • 77
  • 7
  • 1
    It is definitely possible, try: `If IsEmpty(Sheets("Results").Cells(10, 1)) Then` – Victor K Sep 28 '17 at 15:49
  • 1
    Your code seems to work fine for me. – ServerS Sep 28 '17 at 15:49
  • 1
    For the final point of the question; if the columns are contiguous you could use a for loop and iterate the column reference, if not then you could create an array of column references and iterate those. – Zerk Sep 28 '17 at 15:51
  • 1
    It is `Cells(Rows,Columns)` so maybe you are inverting it, and should be `Sheets("Results").Cells(1, 10).Value`. And you can loop from 1 To 9 to check 9 columns. – danieltakeshi Sep 28 '17 at 15:59
  • Where and how are you assigning `L1`? – Mathieu Guindon Sep 28 '17 at 16:00
  • 1
    FWIW `IsEmpty` has nothing to do with cells, and everything to do with `Variant`. It will return `True` if you give it a `Range.Value` that contains *nothing* - not even an empty string. – Mathieu Guindon Sep 28 '17 at 16:01
  • Thank-you eveyrone, I *did* have the row and cell numbers inverted. – rdplanglois Sep 28 '17 at 16:54

2 Answers2

3

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
Dannyg9090
  • 196
  • 8
3

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.

  • This is a gloriously elegant solution, thank-you. Is there something equivalent to `xlCellTypeBlanks` that *will* return true for a zero-length string? – rdplanglois Sep 28 '17 at 16:57
  • For non-formulas (i.e. typed or pasted values) use xlCellTypeConstants which can apply to xlErrors, xlLogical, xlNumbers, xlTextValues or any bitwise combination of those. Values returned by formulas fall into the xlCellTypeFormulas category with the same sub-parameters. See [SpecialCells](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-specialcells-method-excel). –  Sep 28 '17 at 17:05
  • Thank-you @Jeeped. Given the discovery of `xlCellTypeFromulas`, perhaps it would be easier to use that instead of wrestling with `IsEmpty`? Especially since the tested cells aren't *actually* empty, but have `""` in them... – rdplanglois Sep 29 '17 at 16:12