Excel 2007 Move Cell With Text To Top Of Column

Hello,

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.

Thanks!
EE-Sample-2.xlsm
LVL 1
EscanabaAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
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.End(xlDown).ClearContents
cel.Offset(1) = val
Next cel
End Sub
0
 
EscanabaAuthor Commented:
Perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.