Date format Pasting from report not playing Nice

[Dannygreen1992]

Hi All

I'm having a nightmare with a workbook, it has a whole bunch of amateur code, but it worked

however! the report this workbook imported its results from has been changed and the Date formats are acting crazy, swapping the months and days. The issue being that the workbook uses an Index Match formula and can no longer match the dates, it appears the new format adds a null value to each cell

The only thing that has worked is copying the cells into notepad and pasting back into the table, which I'm unsure how to write a module for as I've never activated or worked with notepad in VBA

I need a method of targeting a whole sheet and removing all null values, this is how they appear in the raw report:

Date
07/04/2019
08/04/2019
09/04/2019
10/04/2019
11/04/2019
12/04/2019
13/04/2019

And this is the outcome once the workbook has imported the data and made it into a table, he bottom cell is the only one showing the actual date, but the null value still ruins the index match:

04/07/2019
04/08/2019
04/09/2019
04/10/2019
04/11/2019
04/12/2019
13/04/2019

Cell as Date {dd-mm-yyyy}:

43650
43681
43712
43742
43773
43803
13/04/2019

Cells as text:

04 July 2019
04 August 2019
04 September 2019
04 October 2019
04 November 2019
04 December 2019
13/04/2019

Cells as date

Any and all ideas appreciated, again I can confirm every cell has a null value at the end of the string

Many Thanks

Danny

 

[CalcSux78]

You might try Rick Rothstein's method to clean the spaces and non-printing characters.
http://www.excelfox.com/forum/showthread.php/155-Trim-all-Cells-in-a-Worksheet-VBA#post1092

Here's his function:

Function CleanTrim(ByVal S As String, OptionalConvertNonBreakingSpace As Boolean = True) As String
  Dim X As Long, CodesToClean AsVariant
  CodesToClean = Array(0, 1, 2, 3, 4, 5,6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
                       21, 22, 23, 24,25, 26, 27, 28, 29, 30, 31, 127, 129, 141, 143, 144, 157)
  If ConvertNonBreakingSpace Then S =Replace(S, Chr(160), " ")
  For X = LBound(CodesToClean) ToUBound(CodesToClean)
    If InStr(S, Chr(CodesToClean(X)))Then S = Replace(S, Chr(CodesToClean(X)), "")
  Next
  CleanTrim =WorksheetFunction.Trim(S)
End Function

Then you could use a sub like this to clean your worksheet (modify as needed).

Sub CleanAll()
Dim i&, j&
Dim rng As Range, Arr As Variant
 
Set rng = [A1].CurrentRegion
Arr = rng
 
For i = LBound(Arr, 1) To UBound(Arr, 1)
  For j = LBound(Arr, 2) To UBound(Arr, 2)
    Arr(i, j) = CleanTrim(Arr(i, j))
  Next j
Next i
rng = Arr
End Sub