macro cancel merged cells & text wrap & autofit columns,rows into used range

[Hasson]

hello

I seek for macro to cancel any merged cell and delete text wrap( break line for each cell into columns or rows ) for each cells and autofit for all of the rows & columns and make the normal borders ,formatting .

because I convert the files PDF to EXCEL files when convert all of data ,then don't become arranging like merged cells , text wrap for each cell also the rows, columns are different in high and width sizes . so the macro should be( borders,formatting are normal , the cells are not merged, there is no break line for each cell into column or row , autofit rows , columns)
thanks

 

[sarathc]

You can use this.

Sub Formatting()
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Selection.RowHeight = 15
Selection.ColumnWidth = 8.11
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Cells.EntireColumn.AutoFit
End Sub

Let me know if anything more you need.

 

[Hasson]

thanks for this , but it deletes borders !!

 

[sarathc]

Okay. If you don't want to remove borders, you can remove Selection.Borders commands from the code.

I will paste the updated code below:

Sub Formatting()
Cells.Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.UnMerge
Selection.RowHeight = 15
Selection.ColumnWidth = 8.11
Cells.EntireColumn.AutoFit
End Sub

 

[Hasson]

If you don't want to remove borders

NO NO ! I don't want it . I would keep it .

 

[sarathc]

If you want the borders as it is in your file, use second code. If you wanna remove those border, use first code.

 

[Hasson]

I made the code is short instead of using selection cells . I think this is better .

Sub Formatting()

With sheet3.UsedRange
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.UnMerge
.RowHeight = 15
.ColumnWidth = 8.11
Cells.EntireColumn.AutoFit
End With

End Sub