Mac OS



EXCEL - text alignment

By Blankbit ·
Tags: Mac
So short of using a space or ' to force a cell to clip the visible text when it is left justified (makes cmd/cntrl A not as useful), is there a way to stop Format Cell > Alignment FILL from replicating shorter length values to "fill" the cell. My file has a list of values in the last cell, some very long, some single value. "Fill" is replicating the short lists over and over, which is not what I need for this file.

I just want to clip visible text, not replicate shorter values until it "fills" the space. Or I guess, is there another way to achieve visual clipping of the values in a cell without wrapping or using spaces/'?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

You have to do it within VBA...

by Darryl~ Moderator In reply to EXCEL - text alignment

Here is a method to make all columns, in all worksheets in the activeworkbook, autofit if data is entered that exceeds the current column width.

Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim actCol, curCol
actCol = Target.Column
If actCol > 52 Then
curCol = Chr(Int((actCol - 1) / 52) 64) & _
Chr(Int((actCol - 27) / 26) 64) & _
Chr(Int((actCol - 27) Mod 26) 65)
ElseIf actCol > 26 Then
curCol = Chr(Int((actCol - 1) / 26) 64) & _
Chr(Int((actCol - 1) Mod 26) 65)
curCol = Chr(actCol 64)
End If
Columns(curCol & ":" & curCol).AutoFit
End Sub

With your workbook open, press ALT F11 (Function Key F11)

Double click 'THIS WORKBOOK' in the Microsoft Excel VBA Project objects in the upper left quadrant.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button w/white 'x' --- top right).

Save the workbook.

Enter data in any column, in any sheet in the workbook, and if it exceeds the column width the column will autofit once you select a cell in a different column.

Related Discussions

Related Forums