How to move cells to a another Cell?

  • Thread starter Thread starter Jose
  • Start date Start date
J

Jose

Hello,

I hope someone can help me on this. I am trying to move cells from a group
into a top cell, the initial view of column A (very simplistic) of the cell
looks like this:

1
2
3
4

1
2
2
2

3
3
3

1

1
2
3

What I want to do is to move 2 3 4 from the first group to the cell that
contains the 1 (first entry) and continue with the rest of the cells so at
the end columnA will look like this:

1 2 3 4
1 2 2 2
333
1
123

The cells vary in lenght and I have several spreasheets with LARGE amounts
of data that I have to processed, so I am hoping that there is a macro (VB)
that is able to do that as I am a novice on VB

Cheers!

-Jose

P.s., each group has a blank row in between.
 
Try this against a copy of your worksheet--it destroys the original!

Option Explicit
Sub testme()

Dim myRng As Range
Dim wks As Worksheet
Dim myArea As Range

Set wks = ActiveSheet

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants Found"
Exit Sub
End If

For Each myArea In myRng.Areas
myArea.Copy
myArea(1).Offset(0, 1).PasteSpecial Transpose:=True
Next myArea


'clean up column A
.Columns(1).Delete

'clean up blanks in column b
On Error Resume Next
.Columns(1).Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (testme)
and then click run.

After you're done, you can dump the macro (or keep it for future use???).

But if you're bothered by any "contains macros" warning message when you open
the file, you can read Debra Dalgleish's site to see how to clean it up.

http://www.contextures.com/xlfaqMac.html#NoMacros
 
Back
Top