Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete space in front of date
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete space in front of date
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to delete space in front of date
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove blank space in front of text in multiple cells at once | Excel Discussion (Misc queries) | |||
How to delete a ' from front of data | Excel Discussion (Misc queries) | |||
Remove space in front of number in a cell? | Excel Worksheet Functions | |||
Macro to remove space at front and end of a cell | Excel Discussion (Misc queries) | |||
Macro want delete the date row? | Excel Programming |