GingaNinga
New Member
- Joined
- Sep 1, 2017
- Messages
- 26
- Office Version
- 365
- Platform
- Windows
Hello - I have this VBA code which is actually working great!
As I am still learning VBA, most of this was the result of a recording the steps with some tweaks for auto-filling, and correcting some minor errors in my recording.
In any event, I am wondering if there is a more efficient, or quicker way of getting this code to run. Thanks as always for your help!
As I am still learning VBA, most of this was the result of a recording the steps with some tweaks for auto-filling, and correcting some minor errors in my recording.
In any event, I am wondering if there is a more efficient, or quicker way of getting this code to run. Thanks as always for your help!
VBA Code:
Sub Prep_CallSmartSchedule()
'
' Prep_CallSmartSchedule Macro
'
' Keyboard Shortcut: Ctrl+d
'
' Replaces '=' and '"' with blanks from exported file
Cells.Replace What:="=", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="""", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
, FormulaVersion:=xlReplaceFormula2
' Inserts temporary columns to right of Date
Columns("F:H").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
' Selects Date column, and separates by '/' delimter
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
' Titles Column H as 'Date'
Range("H1").Select
ActiveCell.FormulaR1C1 = "Date"
' Concatenates the temporary Day, Month, Year values as 'MM/DD/YYYY'
Range("H2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]&""/""&RC[-3]&""/""&RC[-1]"
' Auto Fill formula in Column H ('Date') to last row of data found in export file
Range("G1").Activate
Application.CutCopyMode = False
With Range("H2")
.AutoFill Destination:=Range("H2:H" & Range("G" & Rows.Count).End(xlUp).Row)
End With
' Copy and Paste Values in Column H ('Date') to remove concatenation formula
Columns("H:H").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Delete temporary Day, Month, Year columns
Columns("E:G").Select
Selection.Delete Shift:=xlToLeft
' Format Date Column
Columns("E:E").Select
Selection.NumberFormat = "m/d/yyyy"
End Sub