reorganise table

Posted on 2011-10-28
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.
Question by:Saqib Husain, Syed
    LVL 26

    Accepted Solution


    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
    Columns("I:I").ColumnWidth = 44.56
    Columns("I:I").WrapText = True
    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(Selection, ActiveCell.SpecialCells(xlLastCell)).Delete Shift:=xlUp
    ActiveWindow.FreezePanes = True
    Range("Q:S,N:N,M:M,K:K,G:H,E:E,C:C").Delete Shift:=xlToLeft
    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

    LVL 43

    Author Closing Comment

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

    Expert Comment

    Thanks, ssaqibh. Brought back un-fond memories!

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now