VB Autofilter issue - odd behavior if too many columns populated, hides rows outside of range

[AVGeek]

I have been working on a very complex excel sheet for my job - used for estimating equipment, labor, and other costs for a services company. I have most of it working, but ran into an issue when I went to do a beta test. The bill of materials is quite long, and I have "collapse" and "expand" macros to hide/unhide unused rows after the equipment is input. The autofilter keys off of column B, which holds quantity. If cell (B,x) is empty, the row is hidden. This is supposed to function on only rows 13 through 623 as shown in the macro code below.

The issue is that if too many of the columns are populated, the autofilter still works, but my job summary rows (624 through 636) are hid even though they shouldn't be affected due to the specified range. Columns A-AB are used for various pieces of information (quantity, manufacturer, item model, item description, cost, vendor, electrical information, associated labor hours, margin, sell cost, etc.)

I recreated the issue in a separate spreadsheet to make sure my other formulas/conditional formatting/vb functions weren't part of the problem. Only the code below is in my new "test" sheet, along with the column headers and some dummy data.

I'm stumped here - I can't find any "maximum column count" associated with the autofilter function (which, BTW is working). No clue why it's affecting rows outside of the specified range. Any help or troubleshooting tips would be greatly appreciated - been banging my head on this for about a week now.

Sub BOM_Expand()
    Application.ScreenUpdating = False
    Range("b13:b623").AutoFilter
    Application.ScreenUpdating = True
End Sub


Sub BOM_Collapse()
    Application.ScreenUpdating = False
    Call BOM_Expand
    Range("b13:b623").AutoFilter Field:=1, Criteria1:="<>", Visibledropdown:=False
    Application.ScreenUpdating = True
End Sub

 

[RoryA]

If I understand your layout correctly, you need to ensure there is a blank row between your autofill range and the data you want to remain visible (quirk of the autofilter).

 

[AVGeek]

Dang... I hate it when it's that easy of a solution! Thanks, Rory! Seems to be working. I'll kick the tires tomorrow with a full trial equipment list. The Beta test continues!!

Still seems odd that it only hid the summary rows if enough info was populated in the range I was actually trying to modify... the mysteries

 

[RoryA]

If there's a contiguous range larger than the range you specify, it will use that. You'll often see code where people just apply a filter to row1 in order to filter a whole table. (I don't like or recommend that approach but it works most of the time )