One way:-
Sub MoveData()
Dim r As Long
Dim lrow As Long
Application.ScreenUpdating = False
lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = lrow To 1 Step -1
With Cells(r, 1)
If Left(.Value, 9) = "statusmsg" Then
.Offset(, 1).Value = .Value
.ClearContents
End If
End With
Next r
Application.ScreenUpdating = True
End Sub
--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
"eadie" wrote in message
m...
Hi all,
ok, I have sheet of data in the following format:
A B
id1: thisisid1
id2: thisisid2
id3: thisisid3
id4: thisisid4
status:
statusmsg1
statusmsg2
statusmsg3
statusmsg4
statusmsg5
statusmsg6
statusmsg7
id1: thisisid1
id2: thisisid2
id3: thisisid3
id4: thisisid4
status:
statusmsg1
statusmsg2
etc... etc...continues in the same format...
what i need to do is move the status messages from column a to column
b...
so it should look like the following:
A B
id1: thisisid1
id2: thisisid2
id3: thisisid3
id4: thisisid4
status:
statusmsg1
statusmsg2
statusmsg3
statusmsg4
statusmsg5
statusmsg6
statusmsg7
id1: thisisid1
id2: thisisid2
id3: thisisid3
id4: thisisid4
status:
statusmsg1
statusmsg2
etc....etc....
now I currently have the following code, it and it moves the first lot
over, how do i get it to loop and get it to continue moving them over
until they have all been moved?
Sub move_status()
Cells.Find(What:="status:", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Range(Worksheets(1).Cells(6, 1), Worksheets(1).Cells(12,
1)).Activate
Selection.Cut
Range(Worksheets(1).Cells(6, 2), Worksheets(1).Cells(12,
2)).Activate
ActiveSheet.Paste
End Sub
any ideas??
many thanks,
eadie
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.558 / Virus Database: 350 - Release Date: 02/01/2004