Table in state where new rows do not get the formula or formatting from the rest of the rows.

Heh,  OssieMac, have to update my comments.

I am now finding "inconsistent" cases of  the result of running  the  macro.

That is, there are some of the original "error" cases that were corrected after running the macro, as i reported. Whether it was the formula or the format  that was not being carried to a new row, it was "fixed".  But,  i am now finding a case where the format issue is not fixed by runnign the macro. I have no idea why this inconsistency. The case i am now seeing, the format from the first cell in a column is correctly applied to all the existing rows of that column. When the macro finishes, you see the same format for all the rows of the column, matching the first row of the column. But, when i add a new row,  it is still taking the format that was previously "stuck" to that column, not the format that is now visible in every row of the column before the new row is added.  So, the old behavior is back  INTHIS CASE (not in all cases, in "Most" cases the macro has "fixed" the issue after it is run and new rows now get the correct format).

I suspect that there is some "fairly deep" and not so reproducible bug deep in Excel's table code.

If you want to provide a way i can send you this example that is not cured by your macro.

I also understand that this may not be how you want to spend all your time. But if looking at it further interests you, let me know how i might get  the example workbook to you.

Otherwise, i did appreciate the idea of getting a macro that worked more narrowly, also.

Thanks much for all your attention to this!

Would still find it useful to have a macro that works only on "selected" column, as most tables only have the issue in 1 or 2 columns. Although it is more work than the loop to run a macro "1 column at a time", in some ways it feels safer to only mess with columns that have an identified problem, and not the others. I'm going to see if i can modify that macro to do that.

I am a bit busy with other stuff at the moment with visitors and all over the festive season. However, your suggestion is a good one and I think it is worth while adding the functionality. Give me a few days and I will provide options to select individual sheets/all sheets, Individual tables/all tables individual columns/all columns etc. 

I will also add a file dialog so that the macro does not need to be installed in the problem workbook but can be run separately and User selects the required file and then selects the required options as per above. For individual selections, the user should be able to make all of the selections up front (including say multiple different Tables, multiple separate columns etc and the code will process all.

Well, i guess by often, i mean "intermittently".  It seems to happen every few months or so.  Not daily or even weekly.  But enough to be bothersome.

But i have isolated two example tables into a workbook that i could upload, but i do not see a button to upload the workbook.  Let me know how and i will upload it.

One example shows new rows not taking format from the rest of the rows in the same column, and one example shows new rows not taking the formula from the rows above in the same column. In both cases ALL rows above the new row added have either the same formula or the same format. In both tables this problem does not apply to all columns of the newly added row, just some columns.

Let me  know if there is a way to upload.

Heh Ossie...sorry for that previous reply...but now i declare "working like a charm".  Have no ide what happened there for that "weird" result, as i described it. I further reduced the table to 3 rows and 16 columns, ran the macro as written (tho did comment out to loop over worksheets) and it worked perfectly.  Did correctly reset format and formula for a column, and it passed the best test of all, when i added a new row, it correctly had the right formula and format for that new, added row.

I have various other tables with the same issue so will be applying this to some other tables which will be a further test.

This will be a "utility macro" that i'll keep around for when this issue arises.

I will suggest to Microsoft that they either fix this [intermittent] bug or provide UI to do what this macro does.  For instance they could have a right-click item "set column formula" or "set column format". Normally one would not have to use those items, but in cases where "something went amiss" it would be a back door to make sure the user could set these as desired.

Would still find it useful to have a macro that works only on "selected" column, as most tables only have the issue in 1 or 2 columns. Although it is more work than the loop to run a macro "1 column at a time", in some ways it feels safer to only mess with columns that have an identified problem, and not the others. I'm going to see if i can modify that macro to do that.

Thanks very much!!!

Thanks for the code. I am going to study and step through it a little.

I tried it and unfortunately it did not work as written. (don't worry everything is well backed up and protected!). It gave a very odd result, i think which i will describe. I had reduced the table to just 3 rows as a trial (the real one is much larger).  What appeared in column 1 of the table in those 3 rows is exactly the first three Names of my "Defined Names" (which you see in the "Name Manager"). So, for instance the first table column in the first row (not the Header row) has the string "AdjRng"...which is the first (alphabetically) Defined Name in this workbook.  In column 2 of the first row, it has the string that defines the Defined Name, which is the 3rd column of the Name Manager, called "Refers To".  So for instance in Col 2 of my table now, the first row has the string "=Indir_rng!$B$61" (which is the definition of "AdjRng") with the "="  sign, but it is not treated as a formula, as the string is what appears. (And this is not a display issue as all other formulas in the workbook display with their values, not the formula, in the cell).  So, a bit of an odd result!

If you happened to feel like writing more code, I would like to propose that it be more manual, as the issue is only very intermittent with a column here and there.  Perhaps it if operated on one column at a time.  I could make a selection in the column I want to operate on, and then for that column, it would apply the formula to the column (if a formula), and  also the formatting to the column.  Then i could easily test one single column and see how it worked.

Thanks for the effort.  I will be trying to step a little in the code to see if i can understand it better.

Thank you!