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

Sorry, I should add there are currently only 14 rows in the sheet. So
why would a new row start at 2997?

Many thanks!

Excel 2000


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

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

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