CODEWITHSUNDEEP
excelvba
wazami
wazami

Reputation: 23

Using a macro to hide rows without breaking formulas/ functions

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?

Upvotes: 0

Views: 82

Answers (1)

Tim Stack
Tim Stack

Reputation: 3248

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

Upvotes: 0

Related Questions