Select all cells with contents

[0nyx175]

Good afternoon all,

Please see spreadsheet below

(1) (2) (3)
A) Bob 12 carrots
B) Ed 14 Beans
C) James 19 Bacon

This spreadsheet is designed to copy and paste information into the first column, select the cells and convert them using text to columns. Here is my script

 Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(8, 1), Array(13, 1), Array(26, 2), Array(33, 1), _
        Array(49, 1), Array(56, 1), Array(72, 1), Array(77, 1)), TrailingMinusNumbers:=True
 
 
 
    Range("A2").Select

Unfortunately, each time the spreadsheet is downloaded, to be able to convert everything text to columns I need to select each cell which contains information otherwise it wont work (if i select all cells in A1: A10000 it only allows me to do delimited text to columns and I need fixed width.)

Can anyone come up with a simple VB code to highlight all cells in a row which contain information? I dont want all cells, just the ones with something in them.

Thanks

0nyx175

 

[Peter_SSs]

Not sure just where you are headed with this. You cannot use Text to columns on a disjoint range.

 

[0nyx175]

Okay I've confused things.

All i want is a piece of code that selects all cells in a row that contain any information and highlights them.

 

[Peter_SSs]

Does this work for you?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Teat()<br>    Columns("A").SpecialCells(xlCellTypeConstants, 23).Interior.ColorIndex = 4<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[0nyx175]

Does this work for you?


Sub Teat()
Columns("A").SpecialCells(xlCellTypeConstants, 23).Interior.ColorIndex = 4
End Sub

Peter_SSs - you are a bona-fide genius. Many thanks.

0nyx175