Cut & Delete

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
989
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.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
989
Office Version
  1. 365
Platform
  1. Windows
Mike

Nothing much is happening?
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
989
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
989
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,777
Members
414,020
Latest member
Meghdad

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