Runtime error 1004- No data was selected to parse

[Confuzzled_1]

Hi all,

I have the following code that works fine on, let's say, column B. because it has data in it. But as soon as it gets to column C, the column can sometimes be empty but sometimes have data. If it IS empty, I get the above error.

Help...I basically need it to do what I want it to when there is data and then if there isn't, for it to just go onto my next piece of code.

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

Cheers

 

[Richard Schollar]

Test with a find first:

If Not Range("C:C").Find("*") Is Nothing Then  'if it's not nothing then column contains data
 
  'run your code
 
Else
 
  'don't run your code - do something else eg warn user?
 
End If

 

[Confuzzled_1]

Thanks for the fast reply. Unfortunately, I get the same error after changing my code to-

If Not Range("AL:AL").Find("*") Is Nothing Then

Range("AL2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Else
End If

Hmph.

 

[Richard Schollar]

That's because the code in your second post is different from the code in your first post (you selected the entire column in post #1, but not all of it in post #2). You could amend the line to:

If Not Range("AL2:AL" & Rows.Count).Find("*") Is Nothing Then

 

[Confuzzled_1]

OK- so your first solution worked fine once I'd gone through and fixed my shonky code.

Rookie mistake...fix a problem in one place but not another and you just jog the error down your code.

Thanks so much for you help!

 

[ProgramUser]

Running Specs - W7, Office 2010
Hi, I am receiving exactly the same error after successfully stepping through the first part of my code 'after' a data scrape (Success), I then wish for the resultant data to be dropped into a particular worksheet (Success), then have it formatted correctly (this is the problem and where the code stops).

The data scrape results in csv data arriving in at the excel worksheet in one long line (csv format). I have only 8 columns of data to format and thousands of rows. The data drop occurs at the worksheet at Cell B1.

My code (the portion containing the formatting instructions);

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1)), TrailingMinusNumbers:=True
Cells.EntireColumn.AutoFit
Range("A1").Select

I am receiving the error at all lines (red dot)

Does any one have any ideas?