Aamir Hussain
asked on
Unmerged cells of excel Sheet
I am exporting a result file from software.Exported file have many merged rows and columns.I am facing trouble in extracting data from the workbook. I need a formula or VBA code which can unmerge all rows and columns.
Excel-20170731-17213317.xls
Excel-20170731-17213317.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Shums has done most of the work here, but the code can be tidied a little.
Sub UnMergeCells()
Dim Ws As Worksheet
Dim LR As Long, d As Long
Dim c As Range, Rng As Range, ColRng As Range, DelRng As Range
On Error GoTo err_quit
Application.ScreenUpdating = False
Set Ws = Worksheets("Sheet1")
With Ws
LR = .Range("A" & Rows.Count).End(xlUp).Row
Set Rng = .Range("A1:V" & LR)
For Each c In Rng
If c.MergeCells Then c.UnMerge
If c.WrapText = True Then c.WrapText = False
Next
Union(.Columns(3), .Columns(5), .Columns(6), .Columns(8), .Columns(9), .Columns(13), .Columns(15), .Columns(18), .Columns(19), .Columns(21)).Delete Shift:=xlToLeft
.Range("B5:B" & LR).Delete Shift:=xlToLeft
For d = LR To 8 Step -1
If .Cells(d, 1) = "" Then
.Rows(d).Delete
End If
Next d
With .Range("D14:H" & LR)
.Replace What:=".0", Replacement:="", LookAt:=xlPart, MatchCase:=False
.NumberFormat = "0.0"
End With
.Rows.AutoFit
.Columns(1).AutoFit
.Range("B5").ColumnWidth = 12
.Columns("C:L").AutoFit
.Range("A5").Select
End With
err_quit:
Application.ScreenUpdating = True
End Sub
ASKER
Thanks Shums for giving my desired solution as I want.IT will be very helpful for my daily working.
2) You posted and Excel workbook but the outcome when file is opened is a word document.
I suggest that the output of your software be a simple /txt file that you post here and will create a macro to read from this .txt and get it to Excel in columns
gowflow