Clear range of data

[redspanna]

Hi all

Question 1:

Can anybody tell me how I can get vb to clear the contents of cells Q7 through to last populated cell in column Q up 5

So if last populated cell in column Q is cell Q90, I want Q7:Q85 cleared

Question 2:

place the formula

=SUM(E7+H7-K7)/C7

into cell Q7 then copy down to last populated cell in column Q but up 5
so, again if last populated cell in column Q is cell Q90, I want formula added to Q7:Q85 only!

many thanks

 

[stefankemp]

Hi, try this:

sub trythis()

lastrow = cells(rows.count,17).end(xlup).row
range([Q7],cells(lastrow-5,17)).formular1c1 = "=(RC[-12]+RC[-9]-RC[-6])/RC[-14]"

end sub

You don't need to clear the contents before entering the formula, and you don't need to use SUM in your formula. "=(E7+H7-K7)/C7" is sufficient.

 

[VoG]

For 1 try

Dim LR As Long
LR = Range("Q" & Rows.Count).End(xlUp).Row
Range("Q7:Q" & LR - 5).ClearContents

For 2

Dim LR As Long
LR = Range("Q" & Rows.Count).End(xlUp).Row
Range("Q7:Q" & LR - 5).Formula = "=SUM(E7+H7-K7)/C7"