Need help in how to delete certain data

donkey

Board Regular
Joined
Jan 20, 2007
Messages
133
I have a workbook which starts with data in column B. The heading of each set of data starts with Turn No. What im trying to do is to be able to delete that row and the following 2 lines under it. It will then show LTP in column B. What I need doing with that is to be cut and pasted into column A.

This is how it looks at moment.
Book1
ABCDEFGHI
1TurnNoLoco/ActiTrainWorkingTrainCrossDays
2UnitvityArrDepIdRteRefs
3-----------------------------------------------------------------------------------------
4LTPBY1751
Sheet1


And I need it to look like this
Book1
ABCDEF
1
2
3
4LTPBY1751
Sheet1


Can anyone help. I also need it to carry on doing it right the way down the page.

Many Thanks
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Can you be a bit more specific.

Are the first three rows repeated at places all down the page then?

If so do you just want to blank out the cells where they occur?

Dom
 

donkey

Board Regular
Joined
Jan 20, 2007
Messages
133
Found a different way I could get round this.
What I need to know is where in Column B there is the text LTP BYxxx
I need help on a macro on how to copy and paste that into column A right the way down the worksheet.

I have come up with this if someone could help me modify it.


'
Range("B1").Select
Selection.Copy
Range("A1").Select
ActiveSheet.Paste
Range("B10").Select
Columns("A:A").ColumnWidth = 13.71

The row no's where the LTP appear will not always be the same.
 

donkey

Board Regular
Joined
Jan 20, 2007
Messages
133
Domski in response to your post yes the 3 rows are pasted at various locations down the work sheet and if poss I need the rows deleted. But my other post will also sort too if you can help on that.

Thanks
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292

ADVERTISEMENT

Hi,

This should get you on your way hopefully:

Code:
Sub move_stuff()

Dim myRange As Range

For Each myRange In Range([B1], [B65536].End(xlUp))

If InStr(1, myRange.Value, "LTP BY") = 1 Then

 myRange.Offset(0, -1) = myRange.Value
 
 myRange.ClearContents
 
End If
 
Next myRange

End Sub

Hope it helps,

Dom
 

donkey

Board Regular
Joined
Jan 20, 2007
Messages
133
Domski
Sorry mate where it says LTP in column B I need it COPYING into the same row in column A
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292

ADVERTISEMENT

Hi,

Just take out the myRange.ClearContents line should do it then.

Dom
 

donkey

Board Regular
Joined
Jan 20, 2007
Messages
133
Dont want to do anything. I possibly to forgot to mention that after LTP BY it has its own unique number. Maybe thats why. I apologise if I did forget. If it was is there any way it can copy it over with the 4 numbers at ens too.
Thanks
 

donkey

Board Regular
Joined
Jan 20, 2007
Messages
133
ooppsss sorry I worded it wrong. I mean the macro dont want to work.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
donkey

Is it only the LTP BY X values you want to end up with?

If so couldn't you just use a filter?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

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