How do i convert a text to column code block into a loop?

up vote
0
down vote

favorite

I'm using the following codes repeatedly to text to column some data. I'm repeating these lines from ("A:A") to ("DM:DM"). Sometimes the file i want to run this on will have less that that amount of columns. Even if that's the case i made the code to text to column the maximum possible amount of columns. But this makes the execution slow. I want to convert this to a loop where if the next column is blank, the execution will jump to the next block.

Can you good people suggest me a way to do that?

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 1), TrailingMinusNumbers:=True

Thank you!

asked Nov 14 at 11:41

Nick Razzleflamm

You can use offset, so in a loop for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns....else skip Or simply columns(x).
– Nathan_Sav
Nov 14 at 11:56

So for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ? Also can you explain what each line does?
– Nick Razzleflamm
Nov 15 at 11:13

add a comment |

up vote
0
down vote

favorite

Can you good people suggest me a way to do that?

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 1), TrailingMinusNumbers:=True

Thank you!

asked Nov 14 at 11:41

Nick Razzleflamm

You can use offset, so in a loop for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns....else skip Or simply columns(x).
– Nathan_Sav
Nov 14 at 11:56

So for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ? Also can you explain what each line does?
– Nick Razzleflamm
Nov 15 at 11:13

add a comment |

up vote
0
down vote

favorite

Can you good people suggest me a way to do that?

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 1), TrailingMinusNumbers:=True

Thank you!

asked Nov 14 at 11:41

Nick Razzleflamm

Can you good people suggest me a way to do that?

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _

        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _

        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

        :=Array(1, 1), TrailingMinusNumbers:=True

Thank you!

excel vba excel-vba

asked Nov 14 at 11:41

Nick Razzleflamm

asked Nov 14 at 11:41

Nick Razzleflamm

asked Nov 14 at 11:41

Nick Razzleflamm

asked Nov 14 at 11:41

Nick Razzleflamm

asked Nov 14 at 11:41

Nick Razzleflamm

You can use offset, so in a loop for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns....else skip Or simply columns(x).
– Nathan_Sav
Nov 14 at 11:56

So for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ? Also can you explain what each line does?
– Nick Razzleflamm
Nov 15 at 11:13

add a comment |

You can use offset, so in a loop for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns....else skip Or simply columns(x).
– Nathan_Sav
Nov 14 at 11:56

So for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ :=Array(1, 1), TrailingMinusNumbers:=True ? Also can you explain what each line does?
– Nick Razzleflamm
Nov 15 at 11:13

You can use offset, so in a loop for x=1 to 10 if range("a1").offset(0,x).value<>"" then Range("A:A").offset(0,x).texttocolumns....else skip Or simply columns(x).
– Nathan_Sav
Nov 14 at 11:56

for x=1 to 10  	if range("a1").offset(0,x).value<>""  	then Range("A:A").offset(0,x).texttocolumns Destination:=Range("A1"), DataType:=xlDelimited, _ 	TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 	Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ 	:=Array(1, 1), TrailingMinusNumbers:=True

? Also can you explain what each line does?
– Nick Razzleflamm
Nov 15 at 11:13

for x=1 to 10  	if range("a1").offset(0,x).value<>""  	then Range("A:A").offset(0,x).texttocolumns Destination:=Range("A1"), DataType:=xlDelimited, _ 	TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ 	Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _ 	:=Array(1, 1), TrailingMinusNumbers:=True

? Also can you explain what each line does?
– Nick Razzleflamm
Nov 15 at 11:13

add a comment |

2 Answers
2

active

oldest

votes

up vote
0
down vote

To check if a column has any non-blank cells (formula or value) you could employ this condition:

If Range("A:A").SpecialCells(xlCellTypeBlanks).CountLarge < Range("A:A").CountLarge Then

which will allow you to determine if a column is populated in any way and only execute your code if it is.

Be warned that the call to SpecialCells will fail if the entire column is non-blank.

answered Nov 14 at 12:11

NeepNeepNeep

66047

add a comment |

up vote
0
down vote

accepted

I played around with the code a bit and came up with this solution. I don't know if there is any better way to do this but this gets the job done.

If anyone is interested:

On Error Resume Next

For x = 1 To 200

If Not Range("a1").Offset(0, x).IsEmpty(ActiveCell.Value) Then 

Range("A:A").Offset(0, x).TextToColumns Destination:=Range("A1").Offset(0, x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



Next x

edited Nov 19 at 6:57

answered Nov 19 at 6:51

Nick Razzleflamm

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53299425%2fhow-do-i-convert-a-text-to-column-code-block-into-a-loop%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

up vote
0
down vote

To check if a column has any non-blank cells (formula or value) you could employ this condition:

If Range("A:A").SpecialCells(xlCellTypeBlanks).CountLarge < Range("A:A").CountLarge Then

which will allow you to determine if a column is populated in any way and only execute your code if it is.

Be warned that the call to SpecialCells will fail if the entire column is non-blank.

answered Nov 14 at 12:11

NeepNeepNeep

66047

add a comment |

up vote
0
down vote

To check if a column has any non-blank cells (formula or value) you could employ this condition:

If Range("A:A").SpecialCells(xlCellTypeBlanks).CountLarge < Range("A:A").CountLarge Then

which will allow you to determine if a column is populated in any way and only execute your code if it is.

Be warned that the call to SpecialCells will fail if the entire column is non-blank.

answered Nov 14 at 12:11

NeepNeepNeep

66047

add a comment |

up vote
0
down vote

To check if a column has any non-blank cells (formula or value) you could employ this condition:

If Range("A:A").SpecialCells(xlCellTypeBlanks).CountLarge < Range("A:A").CountLarge Then

which will allow you to determine if a column is populated in any way and only execute your code if it is.

Be warned that the call to SpecialCells will fail if the entire column is non-blank.

answered Nov 14 at 12:11

NeepNeepNeep

66047

To check if a column has any non-blank cells (formula or value) you could employ this condition:

If Range("A:A").SpecialCells(xlCellTypeBlanks).CountLarge < Range("A:A").CountLarge Then

which will allow you to determine if a column is populated in any way and only execute your code if it is.

Be warned that the call to SpecialCells will fail if the entire column is non-blank.

answered Nov 14 at 12:11

NeepNeepNeep

66047

answered Nov 14 at 12:11

NeepNeepNeep

66047

answered Nov 14 at 12:11

NeepNeepNeep

66047

answered Nov 14 at 12:11

NeepNeepNeep

66047

add a comment |

up vote
0
down vote

accepted

I played around with the code a bit and came up with this solution. I don't know if there is any better way to do this but this gets the job done.

If anyone is interested:

On Error Resume Next

For x = 1 To 200

If Not Range("a1").Offset(0, x).IsEmpty(ActiveCell.Value) Then 

Range("A:A").Offset(0, x).TextToColumns Destination:=Range("A1").Offset(0, x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



Next x

edited Nov 19 at 6:57

answered Nov 19 at 6:51

Nick Razzleflamm

add a comment |

up vote
0
down vote

accepted

I played around with the code a bit and came up with this solution. I don't know if there is any better way to do this but this gets the job done.

If anyone is interested:

On Error Resume Next

For x = 1 To 200

If Not Range("a1").Offset(0, x).IsEmpty(ActiveCell.Value) Then 

Range("A:A").Offset(0, x).TextToColumns Destination:=Range("A1").Offset(0, x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



Next x

edited Nov 19 at 6:57

answered Nov 19 at 6:51

Nick Razzleflamm

add a comment |

up vote
0
down vote

accepted

I played around with the code a bit and came up with this solution. I don't know if there is any better way to do this but this gets the job done.

If anyone is interested:

On Error Resume Next

For x = 1 To 200

If Not Range("a1").Offset(0, x).IsEmpty(ActiveCell.Value) Then 

Range("A:A").Offset(0, x).TextToColumns Destination:=Range("A1").Offset(0, x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



Next x

edited Nov 19 at 6:57

answered Nov 19 at 6:51

Nick Razzleflamm

I played around with the code a bit and came up with this solution. I don't know if there is any better way to do this but this gets the job done.

If anyone is interested:

On Error Resume Next

For x = 1 To 200

If Not Range("a1").Offset(0, x).IsEmpty(ActiveCell.Value) Then 

Range("A:A").Offset(0, x).TextToColumns Destination:=Range("A1").Offset(0, x), DataType:=xlDelimited, _

TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, 

Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _

:=Array(1, 1), TrailingMinusNumbers:=True



Next x

edited Nov 19 at 6:57

answered Nov 19 at 6:51

Nick Razzleflamm

edited Nov 19 at 6:57

answered Nov 19 at 6:51

Nick Razzleflamm

answered Nov 19 at 6:51

Nick Razzleflamm

answered Nov 19 at 6:51

Nick Razzleflamm

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Cfrgtkky