Cut & Delete

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
Dear All

Due to the way that data comes in to me I have a rather large sheet which I receive on a regular basis where I need to move the data in cell A2 to A3 then delete row 2.

Then repeat this for the remainder of the column so the the value in A4 goes to A5 with row 4 being deleted.

Basically each alternate row is deleted after the transfer of the value in A2, A4, A6 etc.

I hope that my description makes sense and as always many thanks for your help.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
this is untested - be sure to make a copy of your workbook before trying this

Code:
Sub importcleanup()
Dim i As Long, LR As Long
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LR Step 2
    Range("A" & i).Cut Destination:=Range("A" & i + 1)
Next i
End Sub
 
Upvote 0
Hi,

How about this;

Code:
Sub MoveData()

For i = Rows.count - 1 To 2 Step 2
    Range("A" & i + 1) = Range("A" & i)
    Rows(i).Delete
Next i

End Sub
 
Upvote 0
TexasLynn

Thank you for responding so quickly.

The moving of the text is working fine but at present the empty rows still remain.
I can obviously work around that but it would be nice if the blank rows could be deleted in one swoop.
 
Upvote 0
Thats simply because it starts at the bottom and works up, Try;

Code:
Sub MoveData()

Dim lRow As Long

lRow = Range("A" & Rows.count).End(xlUp).Row

For i = Application.Even(lRow) To 2 Step 2
    Range("A" & i + 1) = Range("A" & i)
    Rows(i).Delete
Next i

End Sub
 
Upvote 0
modified slightly

Code:
Sub importcleanup()
Dim i As Long, LR As Long
LR = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -2
    Rows(i).Cut Destination:=Rows(i + 1)
    Rows(i).Delete
Next i
End Sub
 
Upvote 0
TexasaLynn

I'm not sure if it was your intention but I have added your ideas together and voila it works fine.

Thank you so much for taking the time.

Many thanks

Lapta301
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top