Fill the cell from row 10 till end of the sheet for column E

[TC2000]

How do I fill up the cell for colume E from row 10 till the end of the sheet?


Range("E10&quot.Select
Selection.AutoFill Destination:=Range("E10:E100&quot, Type:=xlFillDefault

Best regards

 

[ide]

what value do you want to fill into the range?

Range("E10&quot.Select
Range("E10&quot = "123"
Selection.AutoFill Destination:=Range("E10:E100&quot, Type:=xlFillDefault

ide

 

[TC2000]

What I want to do is to duplicate a column E with the same value of column D starting row 10 till the end of the sheet

Best regards
TC2000

 

[bryanbayfield]

Until the end of teh sheet or the end of the datarange? I'm presuming the lattter:

Range("e10&quot.formula = "=D10"
Range("e10",range("d10&quot.end(xldown).offset(0,1)).filldown

That should do it, unless the range (d10, end of data range) is non-contiguous. Let me know if it is - there are work-arounds.

Bryan.

 

[TC2000]

Bryanbayfield,

It works fine, but I have an addition request.
How do I put a condition in the filling instruction ?
For any column C which begin with "BUY" or "SELL",
copy Column C value to Column D

Thanks in advance

 

[bryanbayfield]

for n = 0 to range("c10",range("c10&quot.end(xldown)).cells.count - 1
if ucase(left(range("c10&quot.offset(n,0).value,3)) = "BUY" or ucase(left(range("c10&quot.offset(n,0).value,4)) = "SELL" Then
range("c10&quot.offset(n,1).value = range("c10&quot.offset(n,0).value
end if
next
Range("e10&quot.formula = "=D10"
Range("e10",range("d10&quot.end(xldown).offset(0,1)).filldown

*****
Once again, only works with contiguous ranges.

Bryan.

 

[SkipVought]

Here's a full solution - Run Main

Sub CopyC()
    Dim rngC As Range
    Const SELL = "SELL"
    Const BUY = "BUY"
    Set rngC = Range(Cells(10, 3), Cells(Cells.Rows.Count, 3))
    For Each cell In rngC
        With cell
            If UCase(Left(.Value, 4)) = SELL Or _
                UCase(Left(.Value, 3)) = BUY Then
                Cells(.Row, .Column + 1).Value = .Value
            End If
        End With
    Next
End Sub
Sub CopyD()
    Dim rngD As Range
    Set rngD = Range(Cells(10, 4), Cells(Cells.Rows.Count, 4))
    For Each cell In rngD
        With cell
            Cells(.Row, .Column + 1).Value = .Value
        End With
    Next
End Sub
Sub Main()
    CopyC
    CopyD
End Sub

metzgsk@voughtaircraft.com

 

[TC2000]

Bryanbayfield,
It works fine. THANKS AGAIN
I just wander if it will speedup the process if I apply autofilter.

Tony

 

[bryanbayfield]

You mean using the autofilter in the Excel interface to decrease the range before executing the macro? It certainly *wouldn't* speed things up (the range the macro goes through would remain unchanged), and it may cause problems with other parts of your macro. What you could do is add something at the start of the macro that filters your range and copies it to another location, from where the rest of teh macro will execute.

Try playing around with filters while a macro is recording and check teh code generated in the VBA environment - this will help you on your way!

Bryan.