Excel 2010 vba to remove trailing zero from date

[wazzulu1]

Hi;

Ran into a data conversion issue, some dates have a trailing zero behind it, have a few thousand rows affected.

Can I use vba to get rid of them, without removing legitimate zeros from the actual dates? Unique values below:

1/1/2007 0
1/1/2010 0
1/1/2013 0
1/1/2014 0
1/1/2015 0
1/1/2016 0
1/2/2013 0
1/2/2014 0
1/2/2015 0
1/2/2016 0
1/3/2006 0
1/3/2007 0
1/3/2015 0
1/3/2016 0
1/4/2006 0
1/5/2006 0
1/5/2007 0
1/5/2015 0
1/5/2016 0
1/6/2007 0
1/6/2015 0
1/6/2016 0
1/7/2013 0
1/7/2015 0
1/7/2016 0
1/8/2007 0
1/8/2013 0
1/8/2014 0
1/8/2016 0
1/9/2007 0
1/9/2013 0
2/1/2006 0
2/1/2013 0
2/1/2014 0
2/1/2015 0
2/1/2016 0
2/2/2006 0
2/2/2014 0
2/4/2013 0
2/5/2013 0
2/8/2013 0
2/8/2015 0
2/9/2014 0
3/1/2013 0
3/1/2014 0
3/1/2015 0
3/1/2016 0
3/2/2006 0
3/3/2006 0
3/3/2014 0
3/5/2012 0
3/7/2006 0
4/1/2014 0
4/1/2015 0
4/1/2016 0
4/3/2006 0
4/3/2013 0
4/3/2014 0
4/4/2014 0
4/6/2010 0
4/6/2012 0
4/6/2013 0
4/7/2009 0
4/8/2011 0
4/9/2014 0
5/1/2012 0
5/1/2013 0
5/1/2014 0
5/1/2015 0
5/1/2016 0
5/4/2012 0
5/4/2013 0
5/4/2014 0
5/5/2014 0
5/7/2010 0
5/7/2014 0
6/1/2012 0
6/1/2014 0
6/1/2015 0
6/1/2016 0
6/2/2009 0
6/5/2006 0
6/5/2009 0
6/7/2015 0
6/8/2006 0
6/8/2015 0
6/9/2005 0
6/9/2006 0
7/1/2012 0
7/1/2014 0
7/1/2015 0
7/1/2016 0
7/2/2010 0
7/2/2014 0
7/3/2009 0
7/3/2013 0
7/5/2011 0
7/5/2013 0
7/6/2006 0
7/7/2006 0
7/7/2014 0
7/9/2012 0
8/1/2012 0
8/1/2013 0
8/1/2014 0
8/1/2015 0
8/1/2016 0
8/2/2013 0
8/2/2016 0
8/4/2006 0
8/4/2015 0
8/6/2013 0
8/7/2006 0
8/7/2014 0
8/8/2013 0
9/1/2012 0
9/1/2014 0
9/1/2015 0
9/1/2016 0
9/2/2013 0
9/4/2013 0
9/5/2013 0

 

[Rick Rothstein]

Select the entire column, press CTRL+H to bring up Excel's Replace dialog box, put a space followed by a zero in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure there are no check marks in the checkboxes, then click the "Replace All" button.

EDIT NOTE: Just realized you wanted a VBA solution. Try this (change the "A" to the column letter designation where your dates are located)...

Sub RemoveZero()
  Columns("A").Replace " 0", "", xlPart, , , , False, False
End Sub

 

[JoeMo]

Or, if you really want to use VBA, select the cells you want to remove the trailing 0 from, and run this macro:

Sub LoseTrailingZero()
'select the range you want to alter, then run this macro
With Selection
    .Replace " 0", ""
End With
End Sub

 

[wazzulu1]

Thank you Rick and Joe for all of your help with this, this worked perfectly, and saved a LOT of manual effort!