weird bug - Excel 2007

[ilia]

I was just running a small macro that processes a datasheet. This is
the code, in the worksheet's module:

With wsh
For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count
Debug.Print iRow
'only process non-zero amounts
If (Me.Cells(iRow, iSourceAnnualAmountCol).Value <> 0)
Then
' process each month's amount
' assumes first month is in column following annual
amount
For iCol = iSourceAnnualAmountCol + 1 To
iSourceAnnualAmountCol + 12
.Cells(iRowDest, iDestLocationCol).Value =
Me.Cells(iRow, iSourceLocationCol).Value
.Cells(iRowDest, iDestDateCol).Value =
Me.Cells(iSourceDateRow, iCol).Value
.Cells(iRowDest, iDestAmountCol).Value =
Round(Me.Cells(iRow, iCol).Value, 2) * 100
.Cells(iRowDest, iDestDescriptionCol).Value = _
Me.Cells(iRow, iSourceFirstName) & " " &
Me.Cells(iRow, iSourceLastName)
iRowDest = iRowDest + 1
Next iCol
End If
Next iRow
End With

I noticed later that I'm missing some of the data, so I did some
debugging - and turned out that UsedRange.Rows.Count was returning one
row less than there actually were in the data sheet! Weird. Turned
out what was causing this was the header row had wrapping turned on,
at least in some cells. As soon as I turned it off, the row count
became normal again.

I'm not sure whether this behavior is known, but it was certainly
unexpected. The only reason I caught it in the first place was
because the resulting spreadsheet is imported into accounting system,
and the debits did not equal the credits so I had to go back and
figure out which numbers went missing and why.

Anyway, thought I'd share - definitely something to look out for.

 

[Bill Renaud]

Hopefully someone from Microsoft will see this thread. You might have to
get an account on the Microsoft Discussion Groups web site:
http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.excel.programming
and report this to them (but I don't know if this will compromise your
real e-mail address!).

I tested the behavior of UsedRange in Excel 2000, and it definitely does
not exhibit this error.

 

[Bill Renaud]

If Microsoft Online Community Support doesn't see this thread, then you
might go to http://support.microsoft.com/contactus/?WS=communities,
scroll down to the "Product Help and Support" section and click on the
link to "Send questions about your Microsoft product to a customer
service representative."

 

[Jim Cone]

What result do you get if you use one of the following to
determine the last row?
'--
iRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

With ActiveSheet.UsedRange
iRow = .Rows(.Rows.Count).Row
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"ilia"
wrote in message
I was just running a small macro that processes a datasheet. This is
the code, in the worksheet's module:
-snip-
For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count

I noticed later that I'm missing some of the data, so I did some
debugging - and turned out that UsedRange.Rows.Count was returning one
row less than there actually were in the data sheet!
-snip-

 

[Ilia]

Well, here's the issue - I can't recreate the problem. Here's what was done:

* a portion of another worksheet, with wrapped headers, was copied into a
new workbook
* more columns were added to the new workbook with some formulas
* the VBA code I posted earlier took the data out of the columns and
formatted them suitable for import into the accounting system - each source
row generated multiple line items

The total of the ImportData worksheet (result of VBA) was not adding up to
the grand total of the source data. I did some debugging and realized that
the last row of source was not getting processed. I added some Debug.Print
lines to tell me which lines were being processed, and isolated the
UsedRange.Rows.Count as the culprit.

I've tried several things, such as inserting more rows, deleting some rows,
and the count was always coming up one short. So, I ended up turning off
wrapping - and the problem went away! But now, if I go to the same worksheet
and turn the wrapping on, the count is still correct. I cannot recreate the
problem.

Once the audit/budget nonsense that's going on right now is over, I'll look
at this in more depth, but as of now I don't have time to repeat what was
originally done step-by-step to isolate the bug.