Hi All

If I do an auto filter on particular column I return just the rows fo
the selection I chose. Great - no problem here.

My question is - if a user does this auto filter and then wishes to ad
a new row, is there a way I always make sure this is written to th
first available free row? At the minute any addition seems to want t
start at row 2887(????).

Any ideas? Any pointers appreciated. Many thanks
Jule

--
Jules
-----------------------------------------------------------------------
JulesM's Profile: http://www.excelforum.com/member.php...fo&userid=2760
View this thread: http://www.excelforum.com/showthread.php?threadid=47171

It sounds like your autofilter range extends all the way down to row 2886.

I'd remove the autofilter and try resetting the used range.

Debra Dalgleish has some techniques for resetting it:
http://www.contextures.com/xlfaqApp.html#Unused

Then select the range (not the whole columns) and reapply
data|filter|autofilter.

JulesM wrote:
>
> Hi All
>
> If I do an auto filter on particular column I return just the rows for
> the selection I chose. Great - no problem here.
>
> My question is - if a user does this auto filter and then wishes to add
> a new row, is there a way I always make sure this is written to the
> first available free row? At the minute any addition seems to want to
> start at row 2887(????).
>
> Any ideas? Any pointers appreciated. Many thanks
> Jules
>
> --
> JulesM
> ------------------------------------------------------------------------
> JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
> View this thread: http://www.excelforum.com/showthread...hreadid=471715

--

Dave Peterson

Hi Dave

Thanks for the response. I removed the filter and reapplied and got rid
of the row 2887 problem.

I applied the filter for the whole sheet because I want to make sure I
catch anything in the column. When use the filter on a particular
selection, still returns all the values I want but now I get a grey
space underneath (presumably because the filter extends to the very
bottom of the column). What I wanted to do was use the filter and then
allow the user to be able to enter a row of data so that it would be
entered on the next available line e.g.

If there were 10 rows of entered data in the spreadsheet and I filtered
for the value (e.g.) "ABC" which returns e.g 5 rows . I then want the
user to be able to insert a new row (values irrelvant) and for this row
to be inserted at row 11. Is this possible?

Hope I'm making some sense!?

Many thanks in advance
Julian


--
JulesM
------------------------------------------------------------------------
JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
View this thread: http://www.excelforum.com/showthread...hreadid=471715

When I apply an autofilter to a limited range (say A1:X99), I can add data to
row 100 and the filter seems to adjust to include that additional row.

If I skip a row (leaving it blank), then excel figures I don't want it part of
that filtered range.

Are you leaving empty rows?

JulesM wrote:
>
> Hi Dave
>
> Thanks for the response. I removed the filter and reapplied and got rid
> of the row 2887 problem.
>
> I applied the filter for the whole sheet because I want to make sure I
> catch anything in the column. When use the filter on a particular
> selection, still returns all the values I want but now I get a grey
> space underneath (presumably because the filter extends to the very
> bottom of the column). What I wanted to do was use the filter and then
> allow the user to be able to enter a row of data so that it would be
> entered on the next available line e.g.
>
> If there were 10 rows of entered data in the spreadsheet and I filtered
> for the value (e.g.) "ABC" which returns e.g 5 rows . I then want the
> user to be able to insert a new row (values irrelvant) and for this row
> to be inserted at row 11. Is this possible?
>
> Hope I'm making some sense!?
>
> Many thanks in advance
> Julian
>
> --
> JulesM
> ------------------------------------------------------------------------
> JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
> View this thread: http://www.excelforum.com/showthread...hreadid=471715

--

Dave Peterson

Ah..Ok..the penny drops. I think I know what I'm doing to cause this.

To explain, at it's simplest my sheet looks like this:

Column A = Date
Column B = Some data
Column C = formula to calculate the Week Commencing date of the value
supplied in Column A

So....in order to ensure that the week commencing date column would
automatically be populated I copied the formula into the entire column
C.

=IF(A1="","",A1+1-WEEKDAY(A1+8-2))

(the IF statement was purely to stop a cell full of ##### being
returned when the vaule in column A was not set - beacuse no data had
yet been entered here).

I want users to be able to pull back data just for one week at a time
using the auto filter on column C. But now I think I understand that
because the formula fills the entire column the filter is applied to
the entire column and then leaves no free rows to input data. Sound
feasible?

Firstly - is there a better way (than pasting formula into entire
column) to be sure that the value in column C will always be
calculated?

Secondly - Is there something clever I can do with (auto) filter to
only filter on rows in Column C where a value is returned OR filter
only on rows where column A contains a value?

Many thanks for your response.
Jules


--
JulesM
------------------------------------------------------------------------
JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
View this thread: http://www.excelforum.com/showthread...hreadid=471715

How about add the formula in column C when the user puts something in column A.
Then you could remove all those "reserved" formulas.

If you want to try it...

rightclick on the worksheet tab that should have this behavior and select view
code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub 'one cell at a time
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error GoTo errHandler:
If IsEmpty(Target) Then
Me.Cells(Target.Row, "C").ClearContents
Else
With Me.Cells(Target.Row, "C")
'.FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-2]+1-WEEKDAY(RC[-2]+8-2))"
.FormulaR1C1 = "=RC[-2]+1-WEEKDAY(RC[-2]+8-2)"
.NumberFormat = "mm/dd/yyyy"
End With
End If

errHandler:
Application.EnableEvents = True

End Sub

If you clear the contents of the cell in column A, the code will clean up column
C. So the formula could be made a bit simpler. (I commented out the
original--you can still use that if you want. Just delete the shorter version.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


JulesM wrote:
>
> Ah..Ok..the penny drops. I think I know what I'm doing to cause this.
>
> To explain, at it's simplest my sheet looks like this:
>
> Column A = Date
> Column B = Some data
> Column C = formula to calculate the Week Commencing date of the value
> supplied in Column A
>
> So....in order to ensure that the week commencing date column would
> automatically be populated I copied the formula into the entire column
> C.
>
> =IF(A1="","",A1+1-WEEKDAY(A1+8-2))
>
> (the IF statement was purely to stop a cell full of ##### being
> returned when the vaule in column A was not set - beacuse no data had
> yet been entered here).
>
> I want users to be able to pull back data just for one week at a time
> using the auto filter on column C. But now I think I understand that
> because the formula fills the entire column the filter is applied to
> the entire column and then leaves no free rows to input data. Sound
> feasible?
>
> Firstly - is there a better way (than pasting formula into entire
> column) to be sure that the value in column C will always be
> calculated?
>
> Secondly - Is there something clever I can do with (auto) filter to
> only filter on rows in Column C where a value is returned OR filter
> only on rows where column A contains a value?
>
> Many thanks for your response.
> Jules
>
> --
> JulesM
> ------------------------------------------------------------------------
> JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
> View this thread: http://www.excelforum.com/showthread...hreadid=471715

--

Dave Peterson

Many Thanks Dave,

Apologies for the delay in reply. Works a treat, thanks!

I have got one question related to multiple rows.....

If I copy a single row and then paste as a new entry (just for exampl
purposes) the code is executed and the formula used in column C , en
result Week commencing date is shown....however if i copy multipl
rows, the code doesn't seem to execute and as a result value in C stay
null.

OK...so I saw the following line in your code:
If Target.Cells.Count > 1 Then Exit Sub 'one cell at a time

and commented it out.

Now when I paste multiple rows the code is executed but only for th
first row pasted - e.g. paste 4 rows, only row 1 diplays a w/c date i
column C

Any ideas?

Many thanks again.
Jule

--
Jules
-----------------------------------------------------------------------
JulesM's Profile: http://www.excelforum.com/member.php...fo&userid=2760
View this thread: http://www.excelforum.com/showthread.php?threadid=47171

I think I'd use a different macro to copy/insert the rows.

David McRitchie has one you could review at:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas


Or you could try this version:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("a:A")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

Application.EnableEvents = False
On Error Resume Next

For Each myCell In Intersect(Target, myRng).Cells
If IsEmpty(myCell) Then
Me.Cells(myCell.Row, "C").ClearContents
Else
With Me.Cells(myCell.Row, "C")
'.FormulaR1C1 _
= "=IF(RC[-2]="""","""",RC[-2]+1-WEEKDAY(RC[-2]+8-2))"
.FormulaR1C1 = "=RC[-2]+1-WEEKDAY(RC[-2]+8-2)"
.NumberFormat = "mm/dd/yyyy"
End With
End If
Next myCell

Application.EnableEvents = True
On Error GoTo 0

End Sub

JulesM wrote:
>
> Many Thanks Dave,
>
> Apologies for the delay in reply. Works a treat, thanks!
>
> I have got one question related to multiple rows.....
>
> If I copy a single row and then paste as a new entry (just for example
> purposes) the code is executed and the formula used in column C , end
> result Week commencing date is shown....however if i copy multiple
> rows, the code doesn't seem to execute and as a result value in C stays
> null.
>
> OK...so I saw the following line in your code:
> If Target.Cells.Count > 1 Then Exit Sub 'one cell at a time
>
> and commented it out.
>
> Now when I paste multiple rows the code is executed but only for the
> first row pasted - e.g. paste 4 rows, only row 1 diplays a w/c date in
> column C
>
> Any ideas?
>
> Many thanks again.
> Jules
>
> --
> JulesM
> ------------------------------------------------------------------------
> JulesM's Profile: http://www.excelforum.com/member.php...o&userid=27609
> View this thread: http://www.excelforum.com/showthread...hreadid=471715

--

Dave Peterson