Weird Excel behavior with Filter and Hidden column

I ran into something odd in Excel in my current project; it seems a lot like a bug to me, but that might also be simply that I know less about Excel than I think I do. Anyways, I have reproduced the issue with a simpler scenario.

The problem goes along these lines: in a spreadsheet, I create headers for data, and write out a few records below through code. Everything works fine – even if I hide a column, data gets properly written to the worksheet.

When I add a Filter to the header row, everything still goes great. But when I filter out some of the values in the filter of one of the columns and hide that column, then the code just doesn’t write any values in that column.

Let’s illustrate: here is some quick and dirty VBA code that generates rows of random numbers, and writes them to a sheet, starting in row 2 (my original code was C# / VSTO, but I figured it would be smart to try out VBA as well):

Dim CurrentRow As Integer

Public Sub WriteData()
    If CurrentRow = 0 Then CurrentRow = 2
    Dim data(1 To 3) As Integer
    data(1) = Int(Rnd * 5)
    data(2) = Int(Rnd * 5)
    data(3) = Int(Rnd * 5)
    Dim sheet As Worksheet
    Set sheet = ActiveSheet
    Dim startCell As Range
    Set startCell = sheet.Cells(CurrentRow, 1)
    Dim endCell As Range
    Set endCell = sheet.Cells(CurrentRow, 3)
    Dim targetRange As Range
    Set targetRange = sheet.Range(startCell, endCell)
    targetRange.Value2 = data
    CurrentRow = CurrentRow + 1
End Sub

If I run the macro WriteData on a sheet, I’ll see something like this:

WritingData

Now let’s add some headers with filters:

AddFilters

Everything is still perfectly normal. Let’s hide column B, add a few records with the macro, and un-hide column B – everything runs fine and dandy.

Now to the not-so-fine part – let’s Filter out a few values from column B, which collapses some rows:

FilterColumn

Hide Column B, run the macro a few times, and un-hide column B – here is what I get:

Ooops

The macro has written values in Columns A and C, but nothing has been written in Column B. Isn’t that weird?

In my project, the issue was even weirder: I got into a situation where the code was writing to a certain range, but values ended up being written shifted by one column to the right. I couldn’t yet write a simple example reproducing that problem, but I’ll try to do it.

As a workaround I ended up de-activating filters when code was writing data to the sheet, but it does sound like a bug to me, if only because the behavior is inconsistent. I can write in a hidden cell, I can write in a Filtered cell, but I can’t write in a cell that is both hidden and filtered, with no warning at all.

Is this a known bug with filters? Has anyone else encountered it?

Comments

Have a comment or a question? Ping me on Twitter, or use the comments section!