Incrementing copy and paste macro

[Wkernan]

Hello everyone, had a project assigned to me and I've never used vba so I'm a bit stuck.

I'm attempting to write a simple piece of code to correct a formatting error in a spreadsheet. To fix the error I need to move a row of data up one row and to the right 2 cells. Then Delete the empty row and repeat until the end of the section that is messed up. I currently have the user selecting the range to copy, the cell to paste to and the row to stop at. The copy and paste aspect is working as intended however the macro isn't incrementing the rows correctly. This plus the deleting of rows I believe is causing the macro to crash. Is it possible to increment the rows of a range? When I try datarange.col = datarange.col + 1 I get an error. Thank you for any help you can give. And if you need any additional information just let me know.

 

[venkat1926]

it would be helpful to help you if you can post the data in a way that the data can be copied to an excel sheet.

you can use commas as delimiters between columns

 

[Wkernan]

Sorry for taking a long time to get back to you, work has been quite busy. is the code I'm working with.

ub Macro4()

Dim x As Integer
Dim DataRange As Range
Dim CorrectRange As Range
Dim EndRow As Range
Dim TempRange As Range

Set DataRange = Selection

Set CorrectRange = Application.InputBox("select Range of cells to be copied to", Type:=8)
Set EndRow = Application.InputBox("Select the last Row to be moved", Type:=8)


For x = DataRange.Row To EndRow.Row
    DataRange.Copy
    CorrectRange.PasteSpecial
    Set TempRange = DataRange
    DataRange.Offset(rowoffset:=(x + 2), columnoffset:=0) = x
    TempRange.EntireRow.Delete
    CorrectRange.Offset(rowoffset:=(x + 1), columnoffset:=0) = x
    Next x
End Sub

 

[venkat1926]

post data sheet (small extract) please then only the macro can be tested and if necessary modified

 

[Wkernan]

Here it is. I'm looking to make rows4-8 look like rows 1-3. So data in row 5 needs to be moved to row 4 then row 5 needs to be deleted. And repeat. The full data sheet is several hundred lines long so this needs to be general i believe

 

[venkat1926]

very sorry. Image is not of much use. I do not know how to convert image into an excel data. hereafter always send at least an extract of the data sheet. if you cannot use the addin of this newsgroup for sending sheet send data row by row with comma or any other delimiter between columns cells. if there is space indicate it as <space> in that cell for example your 5th row will be
"space", 159.60,126,1130, etc etc

try this macro and check

the data starts from A1

Sub test()
Dim r As Range, c As Range, j As Integer, k As Integer
Worksheets("sheet1").Activate
Set r = Range(Range("A1"), Cells(Rows.Count, "A").End(xlUp))
For Each c In r
If c = "" Then
Range(c.Offset(0, 1), c.Offset(0, 1).End(xlToRight)).Copy _
    c.Offset(-1, 0).End(xlToRight).Offset(0, 1)
    End If
Next c
j = Cells(Rows.Count, "A").End(xlUp).Row
For k = j To 1 Step -1
If Cells(k, 1) = "" Then Cells(k, 1).EntireRow.Delete

Next k

End Sub