Use the below function (changing [E:E] to the appropriate range for your needs) to circumvent this issue (or change to any other format such as “mm/dd/yyyy”):
[E:E].Select
With Selection
.NumberFormat = "General"
.Value = .Value
End With
P.S. In my experience, this VBA solution works SIGNIFICANTLY faster on large data sets and is less likely to crash Excel than using the ‘warning box’ method.
Related Posts:
- Object Required Error in excel VBA
- What are .NumberFormat Options In Excel VBA?
- Better way to find last used row
- VBA, if a string contains a certain letter
- Excel VBA Macro: User Defined Type Not Defined
- VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
- Select method of Range class failed via VBA
- Does VBA contain a comment block syntax?
- Why does “Paste Method of Worksheet class failed” occasionally occur?
- How to return a result from a VBA function
- How to clear memory to prevent “out of memory error” in excel vba?
- Use VBA to Clear Immediate Window?
- VBA: Else without If Error
- Recommended IDE for VBA
- ByRef argument type mismatch in Excel VBA
- VBA Error: “Compile error: Expected End Sub”
- Error: “Could Not Find Installable ISAM”
- Loop without Do error
- Row count where data exists
- (Excel) Conditional Formatting based on Adjacent Cell Value
- Test or check if sheet exists
- How to detect if user select cancel InputBox VBA Excel
- macro run-time error ‘9’: subscript out of range
- Why does my VBA code throw an “Invalid outside procedure” error?
- Excel Solver Password: what is it and where can it be acquired/bought?
- Compile error: Next without For || VBA
- Is there a way to crack the password on an Excel VBA Project?
- MS Excel Scatterplot converts Months to Numbers
- What does <> mean?
- Excel to Google Sheets – Error: Formula parse error
- Swap x and y axis without manually swapping values
- Excel: Calculate the frequency of a particular month in a column of date cells
- How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
- How to find a value in an excel column by vba code Cells.Find
- How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
- Insert picture into Excel cell
- What is a correct MIME type for .docx, .pptx, etc.?
- Is there Infinity in Spreadsheets?
- Declaring variables in Excel Cells
- How to loop in excel without VBA or macros?
- What is correct content-type for excel files?
- How do I get countifs to select all non-blank cells in Excel?
- What is the function of FormulaR1C1?
- What exactly is the function of Application.CutCopyMode property in Excel
- Excel – SUMIFS for multiple columns
- Workaround for “Formula omits adjacent cells”
- VBA Excel – Insert row below with same format including borders and frames
- check if a file is open in Python
- Select all the cells below current cell in excel
- Excel: Can I create a Conditional Formula based on the Color of a Cell?
- Excel VBA: Can’t get a match, error “Unable to get the Match property of the WorksheetFunction class”
- How do I open space-delimited file in Microsoft Excel?
- Scraping data from website using vba
- Using SUBSTITUTE with wildcard characters
- Change default ‘delimiters’ in excel
- Adding a newline character within a cell (CSV)
- VB: Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY)
- How to install Date Picker form control in Excel 2016
- How to install Date Picker form control in Excel 2016
- How to fill color in a cell in VBA?
- Shortcut to Apply a Formula to an Entire Column in Excel
- Refreshing all the pivot tables in my excel workbook with a macro
- How to label scatterplot points by name?
- What is the difference between “Form Controls” and “ActiveX Control” in Excel 2010?
- Average in VBA in Excel
- VBA Excel “Compile error: Object Required”
- Choosing paper size (NOT DEFAULT sizes) in excel vba
- “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
- Paste Special error 1004 PasteSpecial method of Range class failed
- Using Excel VBA to run SQL query
- VBA: Getting run-time 1004: Method ‘Range’ of object ‘_Worksheet’ failed when using cells
- Cannot run the macro… the macro may not be available in this workbook
- How to prettyprint a JSON file?
- Substring in excel
- IndexError: too many indices for array
- IndexError: too many indices for array
- ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
- IndexError: too many indices for array
- Change date format in a Java string
- Looking for a clear description of Excel’s .xlsx XML format
- Help needed with Median If in Excel
- Invalid column count in CSV input on line 1 Error
- Invalid column count in CSV input on line 1 Error
- Expected RBRACE, but the bracket is there?
- What Excel formula returns the sheet name?
- How can I remove punctuation from input text in Java?
- excel IF formula Error, “You’ve entered too many arguments”
- Check if a string contains another string
- Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser
- VBScript to open a dialog to select a filepath
- std::string formatting like sprintf
- How to fix file format and extension don’t match?
- Convert java.util.Date to String
- Does VBA have Dictionary Structure?
- How can I remove punctuation from input text in Java?
- How to print elements from array with javascript
- Format JavaScript date as yyyy-mm-dd
- How can I open an Excel file in Python?
- ValueError: cannot index with vector containing NA / NaN values
- How to format numbers as currency strings