setting delimiters for pasted data
 

Hello,
I have a macro that includes a text to columns operation using space as the
delimiter. After running the macro, when I paste more data into an Excel
spreadsheet space is still being used as the delimiter until I shut down and
re-launch Excel. Is there a statement that I can add to my macro that
would reset the delimiter for pasted data to tab?

TIA,

Tom

setting delimiters for pasted data
 

Excel likes to help by remembering settings from the last data|text to columns
that you've used.

I think I'd run a dummy data|text to columns, delimited, but remove all the
checkmarks.

This will reset excel's "memory".

You could even do it in code:

Option Explicit
Sub testme()

Dim dummyCell As Range

With ActiveSheet
Set dummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)
End With

dummyCell.Value = "asdf"

dummyCell.TextToColumns Destination:=dummyCell, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1) ', TrailingMinusNumbers:=True

dummyCell.ClearContents

'try to reset last used cell
Set dummyCell = ActiveSheet.UsedRange

End Sub



Tom Harvey wrote:

Hello,
I have a macro that includes a text to columns operation using space as the
delimiter. After running the macro, when I paste more data into an Excel
spreadsheet space is still being used as the delimiter until I shut down and
re-launch Excel. Is there a statement that I can add to my macro that
would reset the delimiter for pasted data to tab?

TIA,

Tom

--

Dave Peterson

setting delimiters for pasted data
 

Thanks for the suggestions Dave. I will give it a try. I was a little
surprised there isn't some simpler (fewer lines) code to do something like
this, but sometimes things are just not as simple as one imagines.

Tom
"Dave Peterson" wrote in message
...
Excel likes to help by remembering settings from the last data|text to
columns
that you've used.

I think I'd run a dummy data|text to columns, delimited, but remove all
the
checkmarks.

This will reset excel's "memory".

You could even do it in code:

Option Explicit
Sub testme()

Dim dummyCell As Range

With ActiveSheet
Set dummyCell = .Cells.SpecialCells(xlCellTypeLastCell).Offset(1,
1)
End With

dummyCell.Value = "asdf"

dummyCell.TextToColumns Destination:=dummyCell, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1) ', TrailingMinusNumbers:=True

dummyCell.ClearContents

'try to reset last used cell
Set dummyCell = ActiveSheet.UsedRange

End Sub



Tom Harvey wrote:

Hello,
I have a macro that includes a text to columns operation using space as
the
delimiter. After running the macro, when I paste more data into an Excel
spreadsheet space is still being used as the delimiter until I shut down
and
re-launch Excel. Is there a statement that I can add to my macro that
would reset the delimiter for pasted data to tab?

TIA,

Tom

--

Dave Peterson