Macro to run formula only on cells with data

[Guest]

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!!!

 

[Guest]

set last row of data:

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

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


HTH

 

[Guest]

be patient with me...

do you mean I should use your suggestion when I select rows where I want to
paste the formula? For instance...


Range("A7").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[9]&"", ""&RC[10]="", "",IF(RC[1]="""","""",RC[1]),RC[9]&"",
""&RC[10])"
Range("A7").Select
Selection.Copy
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range ("A7:A" & lastrow)

set last row of data:

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

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


HTH

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!!!

 

[Dave Peterson]

I like to just plop the formula into the range--instead of putting it once and
filling down--or copy|pasting.


dim LastRow as long
dim myFormula as string

with activesheet

myformula = "=IF(RC[9]&"", ""&RC[10]=""," _
& """,IF(RC[1]="""","""",RC[1]),RC[9]&"",""&RC[10])"

'I used column B to get the last used row
lastrow = .cells(.rows.count,"B").end(xlup).row

.range("a7:a" & lastrow) = myformula
end with

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!!!

 

[Guest]

Worked perfectly! Thank you!

I like to just plop the formula into the range--instead of putting it once and
filling down--or copy|pasting.


dim LastRow as long
dim myFormula as string

with activesheet

myformula = "=IF(RC[9]&"", ""&RC[10]=""," _
& """,IF(RC[1]="""","""",RC[1]),RC[9]&"",""&RC[10])"

'I used column B to get the last used row
lastrow = .cells(.rows.count,"B").end(xlup).row

.range("a7:a" & lastrow) = myformula
end with

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!!!