Coping cells

jeskit · February 8th, 2011, 08:12 AM

Hi

Does anyone know where it is possible to use a counta result in sheet1 to determine how many columns needs to be auto filled using the forumla in the cells in column E

Code:

 Range("E:E").Select
Selection.AutoFill Destination:=Columns("E:IU"), Type:=xlFillDefault
Range("A1").Select

So far i have the code above but instead of it have columns("E:IU") i need to do this but for the numebr of columns defined in the result of a counta formula

Thanks

Jeskit

sektor · February 18th, 2011, 06:16 AM

Please, describe in more detail what you want to do?

mtranchi · February 18th, 2011, 06:52 AM

you should just use the counta function directly in your code instead. You know that excel 2010 has over a million rows though right? I'm trying to figure out how every cell in column E is part of the source range. Must have a lot of data

Code:

Dim lColCount as Long
Dim rgSource as Range
Dim rgDestination as Range


'this is how you use the counta function in code:
With Sheets("MySheet")
    Set rgSource = .Range("E:E")
    lColCount  = Worksheetfunction.CountA(.Range("WhateverRangeYouWantToUseTheCountAFunctionOn"))
    Set rgDestination = rgSource.Resize(1, lColCount)
    rgSource.AutoFill Destination:=rgDestination, Type:=xlFillDefault

End With