Macros that move data

[the_fitz]

Hi All,

I am a noob to macros but have a need to get some data reformatting done that occurs often in my business.

I start with this...

-- removed inline image ---

Basically, column b's quantities need to get moved to the row below it's associated barcode in column a

I tried auto recording a macro, and it worked ONLY on the first line of the sheet. I will not know how many rows there are, so the macro should run until there is no data in column b.

I get that this requires cells "by reference" and some kind of loop (do:while , I think)

Could someone please help me with my excel 2007 spreadsheet?

Thanks,
the_fitz

 

[guerillaunit]

Hi there,
Give this a try

Sub test()
 
Dim i As Long
Dim x As Long
 
Range("A1").Copy
Range("E1").PasteSpecial
Range("B1").Copy
Range("E2").PasteSpecial
 
i = ActiveSheet.UsedRange.Rows.Count
x = 3
 
For Each cell In Range("A2:B" & i)
    cell.Copy
    Range("E" & x).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Skipblanks:=False, Transpose:=True
    x = x + 1
Next cell
 
Range("A1:B65536").Clear
Range("E1:E65536").Copy
Range("A1").PasteSpecial
Range("E1:E65536").Clear
Range("A1").Select
 
End Sub

 

[BiocideJ]

Welcome to the forum!

This should do the trick.

Sub ReformatColumnToNextRow()
Dim i As Long, b As Variant
For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    b = Cells(i, "B").Value
    Rows(i + 1).Insert (xlShiftDown)
    Cells(i + 1, "A").Value = b
Next
Columns(2).Delete (xlshiftleft)
End Sub

NOTE: Since this deletes data I recommend you test this in a copy of your worksheet first. Although it doesn't actually 'DELETE' it, it moves it in such a way that you can't just copy/paste it back.

 

[the_fitz]

WOW!!!

That did exactly what I wanted and needed.

Thank you VERY much!
The_fitz

 

[hiker95]

the_fitz,


Welcome to the MrExcel forum.


Sample raw data:

Excel Workbook
	A	B
1	Barcode:	Quantity
2	07717846	6
3	810598011384	3
4	07717878	4
5	810598011745	2
6
7
8
9
10
11
Sheet1

After the macro:

Excel Workbook
	A	B
1	Barcode:
2	Quantity
3	07717846
4	6
5	810598011384
6	3
7	07717878
8	4
9	810598011745
10	2
11
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 03/29/2012
' http://www.mrexcel.com/forum/showthread.php?t=625084
Dim r As Long, lr As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 1 Step -1
  Rows(r + 1).Insert
  Cells(r + 1, 1).Value = Cells(r, 2).Value
  Cells(r, 2) = ""
Next r
Application.ScreenUpdating = True
End Sub

Then run the ReorgData macro.

 

[jim may]

Yet Another Way...

Sub RformatBCdata()
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    Cells(i, 1).EntireRow.Insert
Next i
LR = Range("B" & Rows.Count).End(xlUp).Row
    Range("B2:B" & LR).Cut Destination:= _
    Range("B3")
End Sub

 

[Rick Rothstein]

And still one more (I think this one will execute quite quickly)...

Sub MergeColumns()
  Dim X As Long, LastRow As Long, TwoCols As Variant, OneCol As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  TwoCols = Range("A1:B" & LastRow)
  ReDim OneCol(1 To 2 * LastRow, 1 To 1)
  Application.ScreenUpdating = False
  For X = 1 To LastRow
    OneCol(2 * X - 1, 1) = TwoCols(X, 1)
    OneCol(2 * X, 1) = TwoCols(X, 2)
  Next
  Range("A1").Resize(2 * LastRow) = OneCol
  Columns("B").Clear
  Application.ScreenUpdating = True
End Sub

 

[BiocideJ]

LOL, he even got 3 solutions AFTER he had what he needed.
This forum is awesome!
I definitely appreciate seeing all of the variant solutions as it prompts me to think about writing code in new and 'hopefully' more efficient ways.