Select all cells with contents

0nyx175

New Member
Joined
Aug 12, 2009
Messages
45
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

Code:
 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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Not sure just where you are headed with this. You cannot use Text to columns on a disjoint range.
 

0nyx175

New Member
Joined
Aug 12, 2009
Messages
45
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,666
Messages
5,597,456
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top