PDA

View Full Version : VBA: Creating Macro to scroll to a Cell and move information



pdx_2188
03-02-2011, 01:14 PM
I am a Business Analyst for a company, and i run Point of Sale reports monthly. one of the companies that I deal with sends reports that are very difficult to work with EX:


CAP010 CAPGEMINI ENERGY (Customer)
1974028025 (Part Number) 2010 (year) 2 (Month) 2 (QTY)
INTERMEC 1-974028-025 LINE CORD (Description)

292892B21 2010 2 1
HP 292892-B21 DUAL CORE PROCESSOR KIT

300682B21 2010 2 2
HP 300682-B21 4GB PC2100 SDRAM

They Put their Customers name at the top, and move the description of the part number underneath the part number. In order to decipher what I need the format has to be clean. So it has forced me to create a macro that still takes some time that just records the action of moving the description to the right of the quantity, and then I use a find blank cells in the month column and delete rows.

My question is would it be possible to create something that says if Month column is not blank then move one row down and column A to one row up and column E?

I hope this is not overly complicated/confusing.

mdmackillop
03-02-2011, 02:09 PM
Welcome to VBAX
Can you post a sample workbook showing before and after
Use Manage Attachments in the Go Advanced reply section.

pdx_2188
03-02-2011, 02:17 PM
Yes definitely I did not know that it was possible. First one is After.

pdx_2188
03-02-2011, 02:18 PM
Here you go.

Thanks,
jeff

mdmackillop
03-02-2011, 03:08 PM
Option Explicit
Sub MoveCells()
Dim Rw As Long
Dim i As Long
Application.ScreenUpdating = False
Rw = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Rw Step 3
Cells(i, 1).Cut Cells(i - 1, 5)
Next
Range(Cells(1, 1), Cells(Rw, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete xlUp
With Range("A1:E1")
.Insert xlDown
.Offset(-1).Value = Array("Part #", "Year", "Month", "QTY", "Description")
End With
Application.ScreenUpdating = False
End Sub

pdx_2188
03-02-2011, 03:10 PM
Thank you so much!