Questions

EXCEL - text alignment

Tags: Mac
+
0 Votes

EXCEL - text alignment

Blankbit
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/'?

Member Answers

    • +
      0 Votes
      Darryl~ Moderator

      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)
      Else
      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.

    • +
      0 Votes
      Darryl~ Moderator

      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)
      Else
      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.