AutoFilter and adding new rows

J

JulesM

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
 
D

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

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
 
D

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

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
 
D

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
 
J

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

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
 
Top