Excel auto fill column width text help!


Dear! I need help with excel.
I have a mail database with approximately 5-10 columns and 100 - 50000 rows.
I sort them by postal code.
After that I give them autofits in height and width.
I add one row at the beginning.
Now i should somehow need all the colums in the first, empty,
row filled with AxxxxxA (This AxxxA is defined by the columne width but must start with A and ended with A)
But some colums have 3 letters and some 30.
How to make a macro for that?

This is how I have now but I do not have AxxxxA

Sub mmmmAutoWidth()
With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
Selection.Font.Bold = True
    Selection.Font.Bold = False
    With Selection.Font
        .Name = "Arial Narrow"
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
         End With
   Dim mCell As Range
 Application.ScreenUpdating = False

 For Each mCell In ActiveSheet.UsedRange.Rows(1).Cells
 Next mCell
 Application.ScreenUpdating = True

 End Sub




The best way to achieve this might be with cell formatting. Apply a custom format to the cells in your new blank row using the format below.


Enter a single "x" in the cells. The format will write an A on the extreme left and another A at the extreme right, filling the interim with the letter x. If there isn't enough room for another letter x a small space will be left before the final A. The width of the cell will not be changed.

You can use another letter, perhaps a dash. Make sure that the letter specified in the format is the one entered in the cell. Entering another other letter will leave the cell to appear blank.

If it is necessary to achieve this result using VBA just use the same functionality.

With Range("A2:M2")            ' Specify the range as required
    .NumberFormat = ";;;\A*x\A"
    .Value = "x"
End With


This is great.
But I have another problem now.
When I save this file as .csv and when I open it again, the xxx characters have disappeared. That must stay.

silverkalos Dec 2, '17 at 12:39 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login