Macro to delete space in front of date

I have a macro that pulls a date into a column. This column is updated
everyday as the days of the month pass. So, the column for today would have
all the dates of March up to 03-23-05. Tomorrow the column will have all the
dates of March up to 03-24-05.

The problem with this current macro is that it is pulling the dates from
another spreadsheet. This other spreadsheet has 1 space in front of the
date. Because of this, I am unable to change the format of the way I want
the date to look. Right now it looks like this " 03/23/2005". Notice the
space in front of the date. I want it to format like this "23-Mar-05".

Can someone help me create a macro or VB code that I can install in my
current macro to eliminate that space in front of the date? Remember the
column today would have 23 rows, but tomorrow it will have 24 rows because of
the update. I'm sure some sort of relative referencing would be needed?

Please help!!! Thanks!!!

Ryan

Hi Ryan,

Sub Test()
Dim cLastRow As Long
Dim i As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
With Cells(i, "A")
.Value = Right(.Value, Len(.Value) - 1)
End With
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Neutron1871" <[email protected]> wrote in message
news:[email protected]...
> I have a macro that pulls a date into a column. This column is updated
> everyday as the days of the month pass. So, the column for today would
have
> all the dates of March up to 03-23-05. Tomorrow the column will have all
the
> dates of March up to 03-24-05.
>
> The problem with this current macro is that it is pulling the dates from
> another spreadsheet. This other spreadsheet has 1 space in front of the
> date. Because of this, I am unable to change the format of the way I want
> the date to look. Right now it looks like this " 03/23/2005". Notice the
> space in front of the date. I want it to format like this "23-Mar-05".
>
> Can someone help me create a macro or VB code that I can install in my
> current macro to eliminate that space in front of the date? Remember the
> column today would have 23 rows, but tomorrow it will have 24 rows because
of
> the update. I'm sure some sort of relative referencing would be needed?
>
> Please help!!! Thanks!!!
>
> Ryan

Assume it is column C

Sub FixColumnC()
Columns(3).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns(3).NumberFormat = "dd-mmm-yy"
End Sub

--
Regards,
Tom Ogilvy

"Neutron1871" <[email protected]> wrote in message
news:[email protected]...
> I have a macro that pulls a date into a column. This column is updated
> everyday as the days of the month pass. So, the column for today would
have
> all the dates of March up to 03-23-05. Tomorrow the column will have all
the
> dates of March up to 03-24-05.
>
> The problem with this current macro is that it is pulling the dates from
> another spreadsheet. This other spreadsheet has 1 space in front of the
> date. Because of this, I am unable to change the format of the way I want
> the date to look. Right now it looks like this " 03/23/2005". Notice the
> space in front of the date. I want it to format like this "23-Mar-05".
>
> Can someone help me create a macro or VB code that I can install in my
> current macro to eliminate that space in front of the date? Remember the
> column today would have 23 rows, but tomorrow it will have 24 rows because
of
> the update. I'm sure some sort of relative referencing would be needed?
>
> Please help!!! Thanks!!!
>
> Ryan

If that doesn't work and you get this data originally from a web site you
can also try:

Sub FixColumnC_1()
Columns(3).Replace What:=chr(160), Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Columns(3).NumberFormat = "dd-mmm-yy"
End Sub

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Assume it is column C
>
> Sub FixColumnC()
> Columns(3).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> Columns(3).NumberFormat = "dd-mmm-yy"
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Neutron1871" <[email protected]> wrote in message
> news:[email protected]...
> > I have a macro that pulls a date into a column. This column is updated
> > everyday as the days of the month pass. So, the column for today would
> have
> > all the dates of March up to 03-23-05. Tomorrow the column will have
all
> the
> > dates of March up to 03-24-05.
> >
> > The problem with this current macro is that it is pulling the dates from
> > another spreadsheet. This other spreadsheet has 1 space in front of the
> > date. Because of this, I am unable to change the format of the way I
want
> > the date to look. Right now it looks like this " 03/23/2005". Notice
the
> > space in front of the date. I want it to format like this "23-Mar-05".
> >
> > Can someone help me create a macro or VB code that I can install in my
> > current macro to eliminate that space in front of the date? Remember
the
> > column today would have 23 rows, but tomorrow it will have 24 rows
because
> of
> > the update. I'm sure some sort of relative referencing would be needed?
> >
> > Please help!!! Thanks!!!
> >
> > Ryan
>
>

Tom - Your first Macro worked. Thanks!!

Bob - Thanks for the input as well.

"Tom Ogilvy" wrote:

> If that doesn't work and you get this data originally from a web site you
> can also try:
>
> Sub FixColumnC_1()
> Columns(3).Replace What:=chr(160), Replacement:="", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False
> Columns(3).NumberFormat = "dd-mmm-yy"
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > Assume it is column C
> >
> > Sub FixColumnC()
> > Columns(3).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
> > SearchOrder:=xlByRows, MatchCase:=False
> > Columns(3).NumberFormat = "dd-mmm-yy"
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Neutron1871" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a macro that pulls a date into a column. This column is updated
> > > everyday as the days of the month pass. So, the column for today would
> > have
> > > all the dates of March up to 03-23-05. Tomorrow the column will have
> all
> > the
> > > dates of March up to 03-24-05.
> > >
> > > The problem with this current macro is that it is pulling the dates from
> > > another spreadsheet. This other spreadsheet has 1 space in front of the
> > > date. Because of this, I am unable to change the format of the way I
> want
> > > the date to look. Right now it looks like this " 03/23/2005". Notice
> the
> > > space in front of the date. I want it to format like this "23-Mar-05".
> > >
> > > Can someone help me create a macro or VB code that I can install in my
> > > current macro to eliminate that space in front of the date? Remember
> the
> > > column today would have 23 rows, but tomorrow it will have 24 rows
> because
> > of
> > > the update. I'm sure some sort of relative referencing would be needed?
> > >
> > > Please help!!! Thanks!!!
> > >
> > > Ryan
> >
> >
>
>
>