copy data from one column to another on certain condition

[cds]

	a	b	c
1	batsman		runs
2	sunil		14
3	c kapil b salil
4	malcom		28
5	runout
6	peter		49
7	b steeve
8	sachin		35
9	c lily b steev
10	extra		4
11	total		130

<tbody>
</tbody>

Hi, I have data something like aboves

I want help to write vba for copying data in between batsman to extra. Here I want to copy a3 to b2, a5 to b4 & so on whenever c column is blank between batsman & extra . Please help me out


thanks in advance

 

[Fluff]

How about

Sub Batsmen()
   Range("C:C").SpecialCells(xlBlanks).Offset(-1, -1).FormulaR1C1 = "=r[1]c[-1]"
   With Intersect(ActiveSheet.UsedRange, Range("B:B"))
      .Value = .Value
'     [COLOR=#0000ff] .SpecialCells(xlConstants).Offset(1, -1).Clear[/COLOR]
'      [COLOR=#ff0000].SpecialCells(xlConstants).Offset(1, -1).EntireRow.Delete[/COLOR]
   End With
End Sub

If you want to remove the data from col A uncomment the line in blue, or if you want to delete the extra rows uncomment the line in red

 

[cds]

Thanks for help sir, . I get run time error 1004 "application defined error or objecft defined error. I am using excel 2007. Can you please guide in the process


Thanks once again

 

[Fluff]

Which line of code is highlighted when you get the error?

 

[cds]

I get error |run time 1004 - aplication-defined or object-defined error for the following line

Range("C:C").SpecialCells(xlBlanks).Offset(-1, -1).FormulaR1C1 = "=r[1]c[-1]"

 

[Fluff]

Do you have the word Runs in cell C1 as shown in you example?

 

[Peter_SSs]

Another code to test with a copy of your data.

Sub ScoreCard()
  With Range("B2:B" & Columns("A").Find(What:="Extra", LookAt:=xlWhole, MatchCase:=False).Row - 2)
    .Value = Evaluate(Replace(Replace("if(#="""","""",^)", "#", .Offset(0, 1).Address), "^", .Offset(1, -1).Address))
    .Offset(1, -1).Value = Evaluate(Replace(Replace("if(#="""","""",%)", "#", .Offset(1, 1).Address), "%", .Offset(1, -1).Address))
    .Columns.AutoFit
    .Offset(1, -1).SpecialCells(xlBlanks).EntireRow.Delete
  End With
End Sub

 

[cds]

Thank your sir for your time.
Yes, There is word . I modified , it works, but because of word "runs" it is not copying for first player . Probably I have to add row after coulmn word "batting". My new macro is

Sub ColorFruitRedBoldn()


FinalRow = Cells(Rows.Count, 12).End(xlUp).Row
' i = 1
For i = 1 To FinalRow


Range("m:m").SpecialCells(xlBlanks).Offset(1, -1).FormulaR1C1 = "=r[1]c[-1]"
With Intersect(ActiveSheet.UsedRange, Range("B:B"))
.Value = .Value
' .SpecialCells(xlConstants).Offset(1, -1).Clear
' .SpecialCells(xlConstants).Offset(1, -1).EntireRow.Delete
End With

Next i
End Sub


I want run it between batting & Extra only ignore the rows before & after , Plese help me to create loop and add row aftger batting.

thanks

 

[Fluff]

Can you please show an accurate mock-up of your data?
Showing what columns & rows your data is in?

 

[Peter_SSs]

With "Runs" in C1, for layout in post #1 , Fluff's code works for me.

Did you try the other code suggested in post #7 ?