Using a macro to hide rows without breaking formulas/ functions

Question

I have a sheet which I am using to assign tasks to particular departments in a business. Each department spans across the columns A to Y, and is 40 rows long with the first department having the range A6:Y46. The sheet has 15 different departments, the last one ending in row 619.

I am trying to create a macro which hides all the rows where column B is empty (As column A always has something in it). However, the sheet also has lots of formulas and functions calculating things and they keep "breaking".

I recorded a macro where I filtered and then hide all the blank cells. This worked perfectly as I was doing it. But when I executed the macro to retry it all the formulas and functions were errored. None of the rows I am hiding actually contain data used in the functions, but the functions do loop through all the cells to check them for a value. Therefore, they kind of are apart of the functions.

Does anyone know a way I could achieve this?

Are your functions UDF's coded in VBA? If Yes then it would be useful to show those. If they are regular worksheet functions, it would be useful to show those also. In short, it would be useful to show some actual example of something which breaks when rows are hidden. — Tim Williams

Tim Stack Tim Stack · Accepted Answer · 2019-02-05T07:25:02

How about building back up the formulas in the macro you use to hide those rows? This would ensure you that the formulas don't go wild with the cells they're referring to, as you build up those references every time AFTER hiding rows.

One way of doing this is to first clear the cells' contents to avoid any problems overwriting existing data, and then filling them with formulas

With Workbooks("REFERENCE").Sheets("REFERENCE")
    'Code to hide rows

    .Range("B1:B35").ClearContents
    .Range("B1:B35").Formula = "=IF($A$1>B1,B1-B2,"FALSE")" 'example
End With

Using a macro to hide rows without breaking formulas/ functions

1 Answers