Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
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:

More...

#### Need help with F#?

The premier team for
F# training & consulting.