Welcome!
Thanks for the feedback!
A.
On Nov 16, 9:46*pm, 1plane <1man1pl...@googlemail.com> wrote:
> On Nov 16, 8:36*pm, AB <austris.bahanovs...@gmail.com> wrote:
>
>
>
>
>
> > If you only need to achieve what you describe - i.e., to get rid of
> > all the rows <=08/03/2004 in B then i don't think you need to use
> > filter.
> > If getting rid of the rows is the only purpose then you wouldn't need
> > a macro for that to begin with - you could just sort by column B and
> > delete manually all that you don't want to retain. (you can use a
> > helper sort column to revert to the initial sort order if needs be).
> > On the other hand, if you MUST use a macro this might be the way:
>
> > Sub DeleteMyRows()
>
> > * * Dim MaxDate As Date
> > * * Dim ndxR As Long
> > * * MaxDate = #8/3/2004#' Your max date here (hardcoded)
>
> > * * For ndxR = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
> > * * * * With Cells(ndxR, "B")
> > * * * * * * If .Value <= MaxDate Then .EntireRow.Delete
> > * * * * End With
> > * * Next ndxR
>
> > End Sub
>
> > On 16 Nov, 19:42, 1plane <1man1pl...@googlemail.com> wrote:
>
> > > Dear All,
>
> > > I would like to automate the code below:
> > > Sub Macro2()
> > > * * Rows("1:1").Select
> > > * * Selection.AutoFilter
> > > * * Selection.AutoFilter Field:=3, Criteria1:="<=08/03/2004",
> > > Operator:=xlAnd
> > > * * Rows("12:12").Select
> > > * * Range(Selection, Selection.End(xlDown)).Select
> > > * * Selection.Delete Shift:=xlUp
> > > * * Selection.AutoFilter Field:=3
> > > End Sub
>
> > > The problem is that when I use the editor to record the macro, the
> > > range is fixed because I click the cell to autofil. I will have varing
> > > amount of rows and hence the need to automate a code to recognise
> > > this.
>
> > > Below is a sample of what I am trying to do and the data.
>
> > > (1) * Insert Autofilter in header row 1
> > > (2) * Column B custom to less than or equal to 08/03/2004
> > > (3) * Delete entire result with variable autofil of rows upto
> > > 08/03/2004
> > > (4) * View remaining data over 08/03/2004
> > > (5) * Remove Autofilter
>
> > > Invoice * * * * Invoice Date * *Date Paid * * * ** * * * * * *Amount
> > > PKR41 * 03/11/2003 * * *08/06/2004 * * *1222.81
> > > 11803 * 16/01/2004 * * *17/05/2004 * * *1045.75
> > > 011847 *13/02/2004 * * *17/05/2004 * * *199.75
> > > 011881 *27/02/2004 * * *21/07/2004 * * *199.75
> > > 011882 *27/02/2004 * * *21/07/2004 * * *199.75
> > > 011888 *02/03/2004 * * *08/06/2004 * * *504.07
> > > 11886 * 02/03/2004 * * *19/07/2004 * * *6835.42
> > > 11901 * 08/03/2004 * * *08/06/2004 * * *26502.12
> > > 11909 * 10/03/2004 * * *28/05/2004 * * *9165
> > > 011918 *15/03/2004 * * *17/05/2004 * * *199.75
> > > 016996 *03/03/2009 * * *03/04/2009 * * *2162
> > > 016997 *03/03/2009 * * *03/04/2009 * * *3979
> > > 017002 *04/03/2009 * * *03/04/2009 * * *3588
> > > 017034 *12/03/2009 * * *03/04/2009 * * *6171.49
> > > 017036 *13/03/2009 * * *14/04/2009 * * *1403
> > > 017037 *13/03/2009 * * *14/04/2009 * * *3812.25
> > > 017039 *13/03/2009 * * *15/04/2009 * * *97750
> > > 017038 *13/03/2009 * * *14/04/2009 * * *97750
> > > 017052 *23/03/2009 * * *21/04/2009 * * *290.95
>
> > > Kind Regards
>
> > > 1plane- Hide quoted text -
>
> > - Show quoted text -
>
> AB,
>
> That works perfectly.
>
> I needed it coded as it is part of an automated process.
>
> Thank you for your assistance
>
> 1plane- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
