Filters of merged cells Macro help please

I have a table with multiple cells and rows of which some are merged and others are not.( i have been rescently made aware of issues with merged anything and thus been advise to try to aoid using, this is considered moving forward in anything i do) However for now................................

In the 1st 2 columns i use merged cells down for multiple rows. If i apply a filter it returns only 1 row of data.

This is a known issue and there is a work around where by you create a column and create the desired look for the X number of rows. in the column that is showing what it looks like you place in top row the data, select row below and type = and use up arrow key. this is applied to whole section going down which results essentialy as having multiple rows saying what ever was in the 1st cell. Then you copy the format over using the format painter tool / delete that created column, i belive essentialy this covers the rows which contain on each row NAME and covers whole selection in a 1 cell image of sorts?? it works

The problem is that if you enter a new row between the top and bottom row or outside the formated scope it does not display in the filter as you have to repeat the top instructions over and over again. (unless blank is included not helpful if multiple users with new row data.)

Because insertion or deletion of a row is not always in the same place i need a better solution...

in the table i present column A which would be somebodys Name, Column B woulkd be Department name If in this example i insert a new row between 1.1.5 and 1.1.6 and call it 1.1.5.6 and enter data in some further columns it will class this row as a blank and not part of column B filtering or the persons name filtering column A because i have not re appplied the formatting trick.

So what i need is a macro i can 1 hit press that would essentially search for changes in row data and apply the nessesary changes so it would be instantly included in the filter as sitting here creating columns and doing the work around each time i make a change is tedious at best... or a macro that is running in background looking for a change to file to update etc.

Any solutions or ideas welcome

Help Please.xlsx

tried to make a macro to do this but its not working at all.. not even close.
maybe its not possible i dont know any help or ideas welcome.