Need help in how to delete certain data

[donkey]

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
	A	B	C	D	E	F	G	H	I
1		TurnNo	Loco/	Acti	TrainWorking	Train	Cross	Days
2		Unit	vity	Arr	Dep	Id	Rte	Refs
3		-----------------------------------------------------------------------------------------
4		LTPBY1751
Sheet1

And I need it to look like this

Book1
	A	B	C	D	E	F
1
2
3
4	LTPBY1751
Sheet1

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

Many Thanks

 

[Domski]

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]

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]

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]

Hi,

This should get you on your way hopefully:

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]

Domski
Sorry mate where it says LTP in column B I need it COPYING into the same row in column A

 

[Domski]

Hi,

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

Dom

 

[donkey]

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]

ooppsss sorry I worded it wrong. I mean the macro dont want to work.

 

[Norie]

donkey

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

If so couldn't you just use a filter?