I'm trying to create a macro that will confine my text to its own cell (ie so
it doesn't spill over to the next). I know I could use alignment options
'text wrap' or 'shrink to fit' however I prefer what I call the cut-off look
where neither the cell nor the text are resized, just the text cut off from
view at the point that it extends beyond its cell border.

Reason for my preference: usually all I need to see is the first 3 letters
of a cell entry and I like to keep my cell and font cells to a regular size
to reduce mental fatigue.

The macro I created only worked with the particular cell I used in the
recording. I based it around the following 3 steps; move to next cell right,
insert space (thus preventing spillover) retreat to home cell

I suppose I could create a template with the whole sheet preset to this
arrangment but also need to do it retroactively to existing sheets.

Select your range with the text you want to truncate then:

selection.offset(0,1).value=" "

Tim

"Steve" <(E-Mail Removed)> wrote in message
news:11E4F644-781F-46A9-BF3B-(E-Mail Removed)...
> I'm trying to create a macro that will confine my text to its own cell (ie
> so
> it doesn't spill over to the next). I know I could use alignment options
> 'text wrap' or 'shrink to fit' however I prefer what I call the cut-off
> look
> where neither the cell nor the text are resized, just the text cut off
> from
> view at the point that it extends beyond its cell border.
>
> Reason for my preference: usually all I need to see is the first 3 letters
> of a cell entry and I like to keep my cell and font cells to a regular
> size
> to reduce mental fatigue.
>
> The macro I created only worked with the particular cell I used in the
> recording. I based it around the following 3 steps; move to next cell
> right,
> insert space (thus preventing spillover) retreat to home cell
>
> I suppose I could create a template with the whole sheet preset to this
> arrangment but also need to do it retroactively to existing sheets.

Hi Steve -

Similar to Tim's version. Click on any cell anywhere in the column that
contains the text and run this:

Sub cutOff()
With Range(Cells(ActiveSheet.UsedRange.Row, ActiveCell.Column), _
Cells(ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row, _
ActiveCell.Column))
.Offset(0, 1) = Chr(32)
End With
End Sub
---
Jay


"Steve" wrote:

> I'm trying to create a macro that will confine my text to its own cell (ie so
> it doesn't spill over to the next). I know I could use alignment options
> 'text wrap' or 'shrink to fit' however I prefer what I call the cut-off look
> where neither the cell nor the text are resized, just the text cut off from
> view at the point that it extends beyond its cell border.
>
> Reason for my preference: usually all I need to see is the first 3 letters
> of a cell entry and I like to keep my cell and font cells to a regular size
> to reduce mental fatigue.
>
> The macro I created only worked with the particular cell I used in the
> recording. I based it around the following 3 steps; move to next cell right,
> insert space (thus preventing spillover) retreat to home cell
>
> I suppose I could create a template with the whole sheet preset to this
> arrangment but also need to do it retroactively to existing sheets.

Brilliant - worked first time!

"Tim Williams" wrote:

> Select your range with the text you want to truncate then:
>
> selection.offset(0,1).value=" "
>
> Tim
>
> "Steve" <(E-Mail Removed)> wrote in message
> news:11E4F644-781F-46A9-BF3B-(E-Mail Removed)...
> > I'm trying to create a macro that will confine my text to its own cell (ie
> > so
> > it doesn't spill over to the next). I know I could use alignment options
> > 'text wrap' or 'shrink to fit' however I prefer what I call the cut-off
> > look
> > where neither the cell nor the text are resized, just the text cut off
> > from
> > view at the point that it extends beyond its cell border.
> >
> > Reason for my preference: usually all I need to see is the first 3 letters
> > of a cell entry and I like to keep my cell and font cells to a regular
> > size
> > to reduce mental fatigue.
> >
> > The macro I created only worked with the particular cell I used in the
> > recording. I based it around the following 3 steps; move to next cell
> > right,
> > insert space (thus preventing spillover) retreat to home cell
> >
> > I suppose I could create a template with the whole sheet preset to this
> > arrangment but also need to do it retroactively to existing sheets.
>
>
>

Thanks Jay!
Cheers
Steve

"Jay" wrote:

> Hi Steve -
>
> Similar to Tim's version. Click on any cell anywhere in the column that
> contains the text and run this:
>
> Sub cutOff()
> With Range(Cells(ActiveSheet.UsedRange.Row, ActiveCell.Column), _
> Cells(ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row, _
> ActiveCell.Column))
> .Offset(0, 1) = Chr(32)
> End With
> End Sub
> ---
> Jay
>
>
> "Steve" wrote:
>
> > I'm trying to create a macro that will confine my text to its own cell (ie so
> > it doesn't spill over to the next). I know I could use alignment options
> > 'text wrap' or 'shrink to fit' however I prefer what I call the cut-off look
> > where neither the cell nor the text are resized, just the text cut off from
> > view at the point that it extends beyond its cell border.
> >
> > Reason for my preference: usually all I need to see is the first 3 letters
> > of a cell entry and I like to keep my cell and font cells to a regular size
> > to reduce mental fatigue.
> >
> > The macro I created only worked with the particular cell I used in the
> > recording. I based it around the following 3 steps; move to next cell right,
> > insert space (thus preventing spillover) retreat to home cell
> >
> > I suppose I could create a template with the whole sheet preset to this
> > arrangment but also need to do it retroactively to existing sheets.