Excel table auto row not working after deleting a custom format

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
100
Office Version
  1. 2010
Platform
  1. Windows
Hello,

I deleted the custom format m/d/yyyy as Excel kept reverting to the US date format, when I want UK date format. As a consequence of this action, my table won't automatically expand, and code to colour certain columns in the table isn't working.

I use Windows 10 and have 2010 Excel. I have been into Windows settings and checked that the regional and language settings are set to English(UK).

I'm baffled by this and I would appreciate your help in fixing this.

Thank you.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hello,

Did you, by removing the custom format, accidentally remove the table definition too? When you click inside the range, the table context menu appears on the ribbon?

Maybe convert table to range, apply your correct format to each column (maybe even use text to column for your dates to ensure US format is read correctly), reapply table format.

Or another possibility is that you somehow changed this setting (it happened to me too a couple of weeks ago, drove me crazy)
-> file -> Options -> Proofing -> AutoCorrect Options -> AutoFormat As You Type -> Check "Include new rows and columns in table".
1609321586452.png
 
Upvote 0
Hello,

Did you, by removing the custom format, accidentally remove the table definition too? When you click inside the range, the table context menu appears on the ribbon?

Maybe convert table to range, apply your correct format to each column (maybe even use text to column for your dates to ensure US format is read correctly), reapply table format.

Or another possibility is that you somehow changed this setting (it happened to me too a couple of weeks ago, drove me crazy)
-> file -> Options -> Proofing -> AutoCorrect Options -> AutoFormat As You Type -> Check "Include new rows and columns in table".
View attachment 28771
Many thanks for your reply. I've tried both of these options and yes, the table menu appears in the ribbon and the 'include new rows and columns in table' option is checked. What's the emoji for pull your hair out!
 
Upvote 0
Removing a custom format should not affect either of those things. Is the custom format in use on one or more table columns? What is the code that is affected?
 
Upvote 0
Did you try my suggested workaround in which you convert to range and table again?
 
Upvote 0
Removing a custom format should not affect either of those things. Is the custom format in use on one or more table columns? What is the code that is affected?
I'm beginning to think that too. The custom format m/d/yyyy wasn't in use at all but excel kept reverting to this date format and is the reason I deleted it. I have two columns with UK dates and now the custom format has gone, these are showing correctly. I think the code is okay though as it worked fine before I removed the format. Here it is for info:
VBA Code:
Dim LRowA As String
Dim LRowB As String
Dim LRowe As String
Dim LRowf As String
Dim LRowg As String
Dim LRowh As String
Dim LRowi As String
Dim LRowj As String

LRowA = [A65536].End(xlUp).Address
LRowB = [B65536].End(xlUp).Address
LRowe = [e65536].End(xlUp).Address
LRowf = [f65536].End(xlUp).Address
LRowg = [g65536].End(xlUp).Address
LRowh = [h65536].End(xlUp).Address
LRowi = [i65536].End(xlUp).Address
LRowj = [j65536].End(xlUp).Address

Range("A7:" & LRowA).Interior.ColorIndex = 34
Range("B7:" & LRowB).Interior.ColorIndex = 34
Range("e7:" & LRowe).Interior.ColorIndex = 34
Range("f7:" & LRowf).Interior.ColorIndex = 34
Range("g7:" & LRowg).Interior.ColorIndex = 34
Range("h7:" & LRowh).Interior.ColorIndex = 34
Range("i7:" & LRowi).Interior.ColorIndex = 34
Range("j7:" & LRowj).Interior.ColorIndex = 34
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Thanks for investigating this problem.
 
Last edited by a moderator:
Upvote 0
There is nothing there (incomplete though it is) that could depend on a date format, so what exactly do you mean by "isn't working"?
 
Upvote 0
There is nothing there (incomplete though it is) that could depend on a date format, so what exactly do you mean by "isn't working"?
A simple macro navigates to the last blank row of a table, say at cell address A14. Inputting anything into that cell should (it used to anyway) auto expand the table, copying down all formulae. Simply put, the issue is that the cursor navigates to the correct cell but the auto expand feature doesn't turn on, the table exists above the new line.
 
Upvote 0
I was actually talking about the code.

Do other tables still auto-expand?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top