AutoFilter and adding new rows

[JulesM]

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]

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

[Dave Peterson]

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

[JulesM]

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

[Dave Peterson]

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

[JulesM]

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

[Dave Peterson]

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

[JulesM]

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

[Dave Peterson]

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