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:




Comment RSS