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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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
 

lapta301

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

Nothing much is happening?
 

lapta301

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

ADVERTISEMENT

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.
 

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

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
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,941
Messages
5,856,421
Members
431,812
Latest member
Elastomers

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
Top