Excel 2007 Move Cell With Text To Top Of Column

Posted on 2012-08-16
Last Modified: 2012-08-16

I get a raw data dump from another system and it generates a single cell of data, per column, which can appear anywhere starting in row 2 through row 200.  I need a code that will start in column A and look at all cells starting in row 2 through row 200.  Once a cell is identified that contains text, cut that cell and bring it up to row 2.  Then do the same for each column following through column BT.

I've attached a smaller version of my report which can be used for an example.  Sheet1 contains the before and Sheet 2 contains how it should look once this code is ran.

Question by:Escanaba
    LVL 43

    Accepted Solution

    Sub move2row2()
    Dim cel As Range, val As String
    For Each cel In Range("A1:" & Range("A1").End(xlToRight).Address)
    val = cel.End(xlDown).Value
    cel.Offset(1) = val
    Next cel
    End Sub
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  ( Here (http…
    This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now