Setting range in a column where data ends

[saziz]

Hi Guys,
I have two problems I trying to solve.

1) Column A & B has data (may be 500 rows) a formula is written i
Column C and I like to fill it down until column A & B has Data tha
could be 500 to 1000 rows.

2) Then I need to delet cells in column a,b & C if certain cells ar
empty.
Thanks for your help
Sazi

 

[Bernie Deitrick]

Saziz,

The solution for the second part depends on what you mean by empty, and the
solution of the first part may be different if your "empty" cells are truly
empty, or if they appear empty from a formula returning "".

Try selecting your formula cell (after you have entered the formula) and
double clicking the fill handle . That should copy down to match column B's
length.

Post back with what you mean by empty...

HTH,
Bernie
MS Excel MVP

 

[Bob Umlas]

Enter the formula in C1, for example, then double-click the fill handle
(little square at bottom right of selection -- if not there, use
tools/options/Edit tab, check "Allow Cell Drag & Drop).

Not sure what you're asking in Q2.

Bob Umlas
Excel MVP

 

[saziz]

*Saziz,

The solution for the second part depends on what you mean by empty
and the
solution of the first part may be different if your "empty" cells ar
truly
empty, or if they appear empty from a formula returning "".

Try selecting your formula cell (after you have entered the formula
and
double clicking the fill handle . That should copy down to matc
column B's
length.

Post back with what you mean by empty...

HTH,
Bernie
MS Excel MVP

Hi Bernie,
This formula is in the middle of a macro. so I need to determine th
length of the range. If this can be done with a code I will put tha
in.
My Q2 is related to Q1, the formula is such that if the answer is <=
that particular cell will be empty (no data) and I need to delet tha
cell. However I got that working. If you can help me on Q1 That woul
be wonderful.
Thank you for your patience and helping me.
Sazi

 

[Bernie Deitrick]

Saziz,

To enter a formula in cells C1:C?? to match column B:

Range("C1", Range("B65536").End(xlUp)(1, 2)).Formula = "=Your formula here"

where the "=Your formula here" part is the formula as written for cell C1,
with any quotes within the formula doubled. The easiest way to get the
actual syntax is to record a macro where you enter the formula in cell C1
(though you may then need to use .FormulaR1C1 instead of .Formula)

HTH,
Bernie
MS Excel MVP

 

[saziz]

*Saziz,

To enter a formula in cells C1:C?? to match column B:

Range("C1", Range("B65536").End(xlUp)(1, 2)).Formula = "=Your formul
here"

where the "=Your formula here" part is the formula as written fo
cell C1,
with any quotes within the formula doubled. The easiest way to ge
the
actual syntax is to record a macro where you enter the formula i
cell C1
(though you may then need to use .FormulaR1C1 instead of .Formula)

HTH,
Bernie
MS Excel MVP


Hi Bernie,
It seems there isa problem how range is selected, it did not work.
have attached the sheet so that you can take a look.
Thanks
sazi

Attachment filename: book3.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63153

 

[Bernie Deitrick]

Saziz,

Sub MacroForSaziz()
Range("C2", Range("B65536").End(xlUp)(1, 2)).Formula =
"=IF(A3-B2<0,"""",A3-B2)"
End Sub

HTH,
Bernie
MS Excel MVP

 

[saziz]

*Saziz,

Sub MacroForSaziz()
Range("C2", Range("B65536").End(xlUp)(1, 2)).Formula =
"=IF(A3-B2<0,"""",A3-B2)"
End Sub

HTH,
Bernie
MS Excel MVP