0
    ActiveCell.EntireColumn.Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Selection.NumberFormat = "dd-mmm-yy"
HTH
  • 2,031
  • 1
  • 4
  • 10
H.K.
  • 3
  • 6
  • Use an `InputBox` to get an input from the user. Google for the term to find the code. – Variatus Apr 12 '20 at 08:11
  • 1
    You can edit your question by clicking on the edit button bellow your post. If you would add some sample data, the current output and the desired output, you might get an answer. – VBasic2008 Apr 12 '20 at 09:27
  • One of column contains date in format like 11.04.2020 , 12.04.2020. I want to convert it in dd-mmm-yy format by doing text to column , but column is not fixed all the time. Code should be dynamic and should start with selected cell – H.K. Apr 12 '20 at 13:09

1 Answers1

0

I found the solution , Thanks for support , Modified code should be 

ActiveCell.EntireColumn.Select

Dim myRange As Range
Set myRange = Selection

myRange.TextToColumns Destination:=myRange, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd-mmm-yy"
H.K.
  • 3
  • 6