Deleting Rows from using a target cell

[Mike]

Hi

I posted a similar question before but I was wondering if
anybody has further ideas on this.....

It is possible to control the number of rows contained in
a range by entering numerical values in a target cell on a
spreadsheet.

If my range consisted of A2: F30, firstly how would I tell
excel that this is my range and secondly get it to add
and/or delete lines based on the number entered in the
target cell by the user.

The problem I have is that the first column of the range
consists of dates, which in turn are linked to formula. So
if somebody enters say no. "6" in the month cell "A1"I
want the macro to automatically delete a certain number of
lines in the range, and then reformat the entire range by
copying down above formula to end of new range.

This is probably a tall order I'm sure, but any guidance
is much appreciated.

Regards,

Mike

 

[Tom Ogilvy]

Sounds like you want a month's worth of dates in column A and you are
worried about 28, 29, 30, or 31 days in the month.

You could just build conditional formulas and eliminate using code.


in A2

=IF(MONTH(DATE(YEAR(TODAY()),$A$1,ROW()-1))<>$A$1,"",DATE(YEAR(TODAY()),$A$1
,ROW()-1))

then drag fill down to row 32.

You other formulas in the row can key off this column

=if($A2="","",formula)

Regards,
Tom Ogilvy