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

[CTLIEN]

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

 

[Rick Rothstein]

Does this code do what you want?

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]

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]

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.