Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 14. November 2010 18:06

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

11/30/2010 11:54:10 PM #

trackback

In excel: count number of times when a value appears in one column and the cell next to it is less than 2?

Someone referenced this post to answer question "In excel: count number of times when a value appears in one column and the cell next to it is less than 2?"...

Copious-Systems | Reply

2/18/2011 4:23:46 PM #

trackback

Codebix.com - Your post is on Codebix.com

This post has been featured on Codebix.com. The place to find latest articles on programming. Click on the url to reach your post's page.

Codebix.com | Reply

10/18/2018 5:23:16 AM #

questionfocus.com

Pingback from questionfocus.com

Excel VSTO - Edit filtered cell - Strange behavior - QuestionFocus

questionfocus.com | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS