copy data from one column to another on certain condition

[cds]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]batsman[/TD]
[TD] [/TD]
[TD]runs[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] sunil[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] c kapil b salil[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] malcom [/TD]
[TD][/TD]
[TD]28[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]runout[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]peter [/TD]
[TD][/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]b steeve [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]sachin [/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]c lily b steev [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]extra[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]total[/TD]
[TD][/TD]
[TD]130[/TD]
[/TR]
</tbody>[/TABLE]

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 ?