如何仅将文本运行到列IF列包含数据 [英] How to run Text to Columns only IF column contains data

问题描述

我需要在一个范围上运行文本到MACRO列，这取决于导入的数据。我已经有分割vba，只是通过记录它，但它给我一个没有数据解析错误时，列是空白。可能有2列一直到10，所以 我需要使vba忽略分割文本到列，如果列为空白 。

I need to run a text to columns MACRO on a range, which varies based on the data imported. I have the split vba already, simply by recording it, but it gives me a "no data to parse" error when the column is blank. There could be 2 columns all the way to 10, so I need to have the vba ignore the "split text to columns" if the column is blank.

相当新的vba，所以代码是基本的，但并不复杂。以下是我为其中一列录制的拆分：

Fairly new with vba, so the code is basic, but it does not to be complex. Here is the split that I have recorded for one of the columns:

 Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

它还分割列E，G，I和K

It also splits columns E, G, I, and K

推荐答案

您可以创建一个循环，查看每个列，如果 CountA（）为0，则表示没有数据。

You could create a loop, that looks at each column, and if the CountA() is 0, it means there's no data in there.

Sub t()
Dim myCols() As Variant
Dim i As Long

myCols = Array(3, 5, 7, 9, 11)

For i = LBound(myCols) To UBound(myCols)
    If WorksheetFunction.CountA(Columns(myCols(i))) <> 0 Then
        Columns(myCols(i)).TextToColumns Destination:=Cells(1,myCols(i)), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=":", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    End If
Next i

End Sub

如果需要添加/减少列，只需编辑 myCols 数组。

And if you need to add/subtract columns, just edit the myCols array.

这篇关于如何仅将文本运行到列IF列包含数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！