Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

reorganise table

Posted on 2011-10-28
3
Medium Priority
?
205 Views
Last Modified: 2012-05-12
I get this accounts table in a format which is highly inconvenient to work with. It has strings of text which are split into rows instead of wrapping in a cell, numbers which are merged across cells.

Can someone give me a macro to convert this table into a more manageable format with split texts collected together and numbers in the appropriate column and no blank rows or columns so that further working on this file is convenient.
AccountStatment.XLS
0
Comment
Question by:Saqib Husain, Syed
  • 2
3 Comments
 
LVL 26

Accepted Solution

by:
redmondb earned 2000 total points
ID: 37047596
ssaqibh,

Following does it for this file, at least. Be careful, the only intelligence here is whatever Excel brings!

BTW, this was recorded on 2010.
Option Explicit

Sub Tidy_Bank()
Dim xLast_Row As Long

With Cells
    .VerticalAlignment = xlTop
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
End With
Cells.EntireColumn.AutoFit
Columns("I:I").ColumnWidth = 44.56
Columns("I:I").WrapText = True
Cells.EntireRow.AutoFit
Cells.AutoFilter
xLast_Row = Range("A1").SpecialCells(xlLastCell).Row
ActiveSheet.Range("$B$1:$V" & xLast_Row).AutoFilter Field:=1, Criteria1:="="
ActiveSheet.Range("$B$1:$V" & xLast_Row).AutoFilter Field:=8, Criteria1:="="
ActiveSheet.Range("$B$1:$V" & xLast_Row).AutoFilter Field:=9, Criteria1:="="
Range("B3").Activate
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Delete Shift:=xlUp
Cells.AutoFilter
Range("I8").Select
ActiveWindow.FreezePanes = True
Range("Q:S,N:N,M:M,K:K,G:H,E:E,C:C").Delete Shift:=xlToLeft
Columns("E:E").EntireColumn.AutoFit
Range("F2:F4").Cut Destination:=Range("E2:E4")
Columns("F:F").Delete Shift:=xlToLeft
Range("H5:H6").Cut Destination:=Range("I5:I6")
Columns("H:H").Delete Shift:=xlToLeft
Range("D5:D6").Cut Destination:=Range("C5:C6")
Columns("D:D").Delete Shift:=xlToLeft
Range("G5:H6").Cut Destination:=Range("F5:G6")
Range("A:A").Delete Shift:=xlToLeft

End Sub

Open in new window

Regards,
Brian.
0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 37047660
Thanks. Saved me some time.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37047672
Thanks, ssaqibh. Brought back un-fond memories!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question