Macro to convert given/already-selected column from text to date format

CTLIEN

New Member
Joined
Jun 6, 2014
Messages
5
Hi Everyone!

Excited to put in my first ever post, been a fan of the forums and use it as a resource all the time.

I frequently format reports several times a week and one of the things I do fairly often is convert a column from text to date format on varying columns. More specifically I'll do the following manually each time on whatever column I need to:

Select the column of an active cell (ctrl+shift)
Do text to column, selecting date MDY on step 3 of 3
Go to format cells and within category date select MM/DD/YY
Align text left (alt, H, AL)


Conceptually it seems like this should be a simple macro, so I tried my usual reverse-engineering-method of editing a recorded macro of my specific actions. The main problem that arises from that is the recording macro chooses a specific cell or row I'm on, but I need a more general macro that just uses active cell to select the entire column. For example, I've copy pasted the recorded macro below and it specifies column G or cells within column G, but I'd like this macro to be more versatile and just select the range of whatever cell I'm currently on. I know there are certain ways to select acive.cells etc. but I think I got stuck when I tried applying it to this component from below "Destination:=Range("G1")".

I'm an eager user/student of Excel and just learning how to do VBA so the more you can break down the why of whatever it is I need to do the more helpful it will be! Of course I enjoy the simplest and most elegant code as much as the next Excel aficionado, so if you can take out any of the extra stuff and let me know what that is that'd be awesome. Much appreciated!

Sub tryharder()
'
' tryharder Macro
'
' Keyboard Shortcut: Ctrl+j
'
Columns("G:G").Select
Range("G6").Activate
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,872
Office Version
  1. 2010
Platform
  1. Windows
Does this code do what you want?
Code:
Sub MakeDates()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, Selection(1).Column).End(xlUp).Row
  With Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    .TextToColumns .Cells, xlDelimited, , , , , , , False, , Array(1, 3)
    .NumberFormat = "mm/dd/yy"
    .HorizontalAlignment = xlLeft
  End With
End Sub
By the way, although you can, you do not have to select the entire column... selecting any cell in the column will also work.
 

CTLIEN

New Member
Joined
Jun 6, 2014
Messages
5
Worked like a charm! Thank you so much.

I was able to deduce most of the code, but could you help explain the following line:

LastRow = Cells(Rows.Count, Selection(1).Column).End(xlUp).Row

I'm assuming this is assigning a numeric value to the newly defined term LastRow by counting the number of rows and maybe using the selection method control+up? Appreciate anyone taking the time to break it down for me, helps me learn and apply it better in the future.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,872
Office Version
  1. 2010
Platform
  1. Windows
Worked like a charm! Thank you so much.

I was able to deduce most of the code, but could you help explain the following line:

LastRow = Cells(Rows.Count, Selection(1).Column).End(xlUp).Row

I'm assuming this is assigning a numeric value to the newly defined term LastRow by counting the number of rows and maybe using the selection method control+up? Appreciate anyone taking the time to break it down for me, helps me learn and apply it better in the future.
Rows.Count equals the number of rows on the worksheet.

Selection(1) is the first cell of the selection... the 1 in parentheses guarantees only the first cell of the selection is looked at (useful in case the selection consists of multiply cells.

Selection(1).Column is the column number of the first cell in the selection.

Cells is an alternative way (to Range) to reference a single cell.

So Cells(Rows.Count, Selection(1).Column) references the last cell on the worksheet in the column occupied by the first cell in the selection.

End(xlUp) says to look up from that last cell until you find a non-blank cell (which is the last cell in that column with data).

.Row returns the row number for that last cell in the column with data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,357
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top