CODEWITHSUNDEEP
excelvba
Cove
Cove

Reputation: 709

Select all cells with values in A column and text to Columns with VBA

my task is:

1) Select all cells in A row which contain values (done). 2) Text to Columns - example value 2017.01.01

Sub selectAndToColumns()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & LR).SpecialCells(xlCellTypeConstants, 23).Select
'2nd step
    Selection.TextToColumns Destination:=Range(A1), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1))
End Sub

1 step is ok, but how can I use my selection instead of Range(A1) in 2nd step? Can I make a variable and how?

Upvotes: 0

Views: 4171

Answers (2)

Xabier
Xabier

Reputation: 7735

I'm not too sure about your requirements, but this will perform the TextToColumns without selecting anything (You can still change the destination to wherever you want the resulting data to be placed, you can do this by specifying a range or even using a variable where your range is stored):

Sub selectAndToColumns()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & LR).SpecialCells(xlCellTypeConstants, 23).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1))
End Sub

UPDATE

If you want to replace your destination with a variable instead of Range("A1") then something like below will work:

Sub selectAndToColumns()
    Dim DestinationRange As String
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    DestinationRange = "D1"
    MsgBox DestinationRange
    Range("A1:A" & LR).SpecialCells(xlCellTypeConstants, 23).TextToColumns Destination:=Range(DestinationRange), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
    :=".", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1))
End Sub

Upvotes: 1

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

I'm not sure if I have understood your question properly, but if you just want to select Column A and paste it as values, I would use:

Columns("A:A").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False
Application.CutCopyMode = False

Upvotes: 1

Related Questions