Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Excel auto fill column width text help!

0

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()
Cells.Select
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
 mCell.EntireColumn.AutoFit
 Next mCell
 
 Application.ScreenUpdating = True
  Selection.Rows.AutoFit
  Selection.Columns.AutoFit

 
 End Sub

Answer
Discuss

Answers

0

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.

;;;\A*x\A

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
Discuss

Discussion

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