set last row of data:

lastrow=CELLS(rows.count,"A").end(xlup).row .... last row in Column A

then Range("A7:A" & lastrow) .....??


HTH

"Rob" wrote:

I put in the range A7:A5000 so that I made sure the formula ran for all of my
cells, but it is putting in a blank value in all cells below the used rows.
Each file I run this macro on will include a different number of rows. So, I
need to paste the forumla only on rows that have data (are not empty). For
instance, select rows with data, and then paste formula. Right now I have...

Range("A7").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"",
""&RC[10])"
Range("A7").Select
Selection.Copy
Range("A7:A5000").Select
ActiveSheet.Paste

What happens is that when I print the file, it is trying to print rows 1 -
5000!!!