Hi!

I have 2 doubts. Please clarify these:

1) How to determine how many rows in a sheet are filled with data?
2) I want to autofill entry in cell AD3 in all the rows (containing data), below it. How shud I do it. I tried it thru recording macro. But then, the no. of rows got fixed. What will i do when tmrw the number of rows will change. Here is the code that needs change:

Range("AD3").Select
Selection.AutoFill Destination:=Range("AD3:AD161"),Type:=xlFillDefaul t

I do not want the range AD3:AD161 to be fixed, as the no. of rows will keep changing.

Please help with both the doubts!
Thanks
Ashu

The AutoFill method will change the value in all cells of the range.

If only cells which already have a value should be changed, loop through the range and determine if the cell is not empty before writing a new value.

This is an AutoFill ex:

Dim Rw As Long
Dim SourceRng As Range
Dim TargetRng As Range

Set SourceRng = ActiveSheet.Range("AD3")
Rw = Range("AD65536").End(xlUp).Row
Set TargetRng = ActiveSheet.Range("AD3:AD" & Rw)
SourceRng.AutoFill Destination:=TargetRng