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

Question

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?

I understand your problem.... TextToColumns will work on an entire column rather than some set of cells within that column. — Gary's Student
– Gary's Student, Commented Jan 11, 2018 at 15:06
I agree that no need in step 1 - i need just select the whole column, but how? — Cove
– Cove, Commented Jan 11, 2018 at 15:26

Xabier · Accepted Answer · 2018-01-11 16:28:21Z

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

Foxfire And Burns And Burns · Accepted Answer · 2018-01-11 16:05:26Z

1

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

answered Jan 11, 2018 at 16:05

Foxfire And Burns And Burns

12.1k2 gold badges19 silver badges43 bronze badges

1 Comment

Cove Over a year ago

Selecting working even in my macro but i needed text to column after. Thanks for your answer anyway.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related