criteria for a given date equal or -greater then-

Hi,
The below given code accepts a date (in "dd.mm.yyyy" format) entered into
the cell G3 of worksheet
"ARAMA" and then displays the records from the worksheet named "LISTE" that
matches with this criteria.
I need to modify it so that when I enter a date into Sheet "ARAMA" G3, I
want to display all records that has a date equal to or greater (newer) then
that date. Can experts here comment on modifying my code?
TIA

'--------------------------------
Public Sub Aratarih()

Range("I2").Value = Time
Range("I1").Value = Date

Range("A6:I2500").Select
Selection.ClearContents
ARANAN = Worksheets("ARAMA").Cells(3, 7)
Worksheets("ARAMA").Cells(3, 7).Activate
If ARANAN = "" Then
Range("A6:I2500").Select
Selection.ClearContents
Worksheets("ARAMA").Cells(4, 9).Value = ""
Worksheets("ARAMA").Cells(3, 7).Activate
'Range("I2").Value = Time
'Range("I1").Value = Date
Exit Sub
End If
With Worksheets("LISTE").Range("D2:D62500")
Set C = .Find(ARANAN, LookIn:=xlValues)
If Not C Is Nothing Then
firstAddress = C.Address
SAY = 0
Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
Range("I2").Value = Time
Range("I1").Value = Date

Do
Set C = .FindNext(C)

adres = C.Address
adres = Right(adres, Len(adres) - 1)
ky = InStr(1, adres, "$", 1)
AD1 = Right(adres, Len(adres) - ky)
Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
SAY = SAY + 1
Loop While Not C Is Nothing And C.Address <> firstAddress
End If
Worksheets("ARAMA").Cells(4, 9).Value = SAY
End With
Range("G3").Select
End Sub

Think you would have to just loop through your column of dates and test each
one.

Hard to tell what you are doing - another possibility might be to use an
advanced filter or an autofilter.

--
Regards,
Tom Ogilvy

"J_J" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> The below given code accepts a date (in "dd.mm.yyyy" format) entered into
> the cell G3 of worksheet
> "ARAMA" and then displays the records from the worksheet named "LISTE"
that
> matches with this criteria.
> I need to modify it so that when I enter a date into Sheet "ARAMA" G3, I
> want to display all records that has a date equal to or greater (newer)
then
> that date. Can experts here comment on modifying my code?
> TIA
>
> '--------------------------------
> Public Sub Aratarih()
>
> Range("I2").Value = Time
> Range("I1").Value = Date
>
> Range("A6:I2500").Select
> Selection.ClearContents
> ARANAN = Worksheets("ARAMA").Cells(3, 7)
> Worksheets("ARAMA").Cells(3, 7).Activate
> If ARANAN = "" Then
> Range("A6:I2500").Select
> Selection.ClearContents
> Worksheets("ARAMA").Cells(4, 9).Value = ""
> Worksheets("ARAMA").Cells(3, 7).Activate
> 'Range("I2").Value = Time
> 'Range("I1").Value = Date
> Exit Sub
> End If
> With Worksheets("LISTE").Range("D2:D62500")
> Set C = .Find(ARANAN, LookIn:=xlValues)
> If Not C Is Nothing Then
> firstAddress = C.Address
> SAY = 0
> Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
> Range("I2").Value = Time
> Range("I1").Value = Date
>
> Do
> Set C = .FindNext(C)
>
> adres = C.Address
> adres = Right(adres, Len(adres) - 1)
> ky = InStr(1, adres, "$", 1)
> AD1 = Right(adres, Len(adres) - ky)
> Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
> Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
> Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
> Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
> Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
> Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
> Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
> Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
> Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
> Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
> Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
> Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
> Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
> SAY = SAY + 1
> Loop While Not C Is Nothing And C.Address <> firstAddress
> End If
> Worksheets("ARAMA").Cells(4, 9).Value = SAY
> End With
> Range("G3").Select
> End Sub
>
>
>
>

Hi Tom,
I do not want to use an advanced filter or an autofilter although it appears
to be the easiest way out. Becuse the user of this workbook will be a
newbee.
The records are in sheet called LISTE as shown in the code, and all I need
to do is to check the dates (excluding empty cells) column there regarding
the date I'll enter on sheet ARAMA, and display the records on sheet ARAMA
with the same or newer dates. Hope I did explain what I intend to do...
Regards
J_J

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Think you would have to just loop through your column of dates and test
> each
> one.
>
> Hard to tell what you are doing - another possibility might be to use an
> advanced filter or an autofilter.
>
> --
> Regards,
> Tom Ogilvy
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>> The below given code accepts a date (in "dd.mm.yyyy" format) entered into
>> the cell G3 of worksheet
>> "ARAMA" and then displays the records from the worksheet named "LISTE"
> that
>> matches with this criteria.
>> I need to modify it so that when I enter a date into Sheet "ARAMA" G3, I
>> want to display all records that has a date equal to or greater (newer)
> then
>> that date. Can experts here comment on modifying my code?
>> TIA
>>
>> '--------------------------------
>> Public Sub Aratarih()
>>
>> Range("I2").Value = Time
>> Range("I1").Value = Date
>>
>> Range("A6:I2500").Select
>> Selection.ClearContents
>> ARANAN = Worksheets("ARAMA").Cells(3, 7)
>> Worksheets("ARAMA").Cells(3, 7).Activate
>> If ARANAN = "" Then
>> Range("A6:I2500").Select
>> Selection.ClearContents
>> Worksheets("ARAMA").Cells(4, 9).Value = ""
>> Worksheets("ARAMA").Cells(3, 7).Activate
>> 'Range("I2").Value = Time
>> 'Range("I1").Value = Date
>> Exit Sub
>> End If
>> With Worksheets("LISTE").Range("D2:D62500")
>> Set C = .Find(ARANAN, LookIn:=xlValues)
>> If Not C Is Nothing Then
>> firstAddress = C.Address
>> SAY = 0
>> Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
>> Range("I2").Value = Time
>> Range("I1").Value = Date
>>
>> Do
>> Set C = .FindNext(C)
>>
>> adres = C.Address
>> adres = Right(adres, Len(adres) - 1)
>> ky = InStr(1, adres, "$", 1)
>> AD1 = Right(adres, Len(adres) - ky)
>> Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
>> Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
>> Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
>> Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
>> Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
>> Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
>> Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
>> Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
>> Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
>> Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
>> Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
>> Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
>> Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
>> SAY = SAY + 1
>> Loop While Not C Is Nothing And C.Address <> firstAddress
>> End If
>> Worksheets("ARAMA").Cells(4, 9).Value = SAY
>> End With
>> Range("G3").Select
>> End Sub
>>
>>
>>
>>
>
>

If the code is applying and removing the filter, extensibly outside the
purview of the user, I am not sure what bearing the user's skill set has on
the code.

--
Regards,
Tom Ogilvy

"J_J" <[email protected]> wrote in message
news:%[email protected]...
> Hi Tom,
> I do not want to use an advanced filter or an autofilter although it
appears
> to be the easiest way out. Becuse the user of this workbook will be a
> newbee.
> The records are in sheet called LISTE as shown in the code, and all I need
> to do is to check the dates (excluding empty cells) column there regarding
> the date I'll enter on sheet ARAMA, and display the records on sheet ARAMA
> with the same or newer dates. Hope I did explain what I intend to do...
> Regards
> J_J
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > Think you would have to just loop through your column of dates and test
> > each
> > one.
> >
> > Hard to tell what you are doing - another possibility might be to use an
> > advanced filter or an autofilter.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "J_J" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >> The below given code accepts a date (in "dd.mm.yyyy" format) entered
into
> >> the cell G3 of worksheet
> >> "ARAMA" and then displays the records from the worksheet named "LISTE"
> > that
> >> matches with this criteria.
> >> I need to modify it so that when I enter a date into Sheet "ARAMA" G3,
I
> >> want to display all records that has a date equal to or greater (newer)
> > then
> >> that date. Can experts here comment on modifying my code?
> >> TIA
> >>
> >> '--------------------------------
> >> Public Sub Aratarih()
> >>
> >> Range("I2").Value = Time
> >> Range("I1").Value = Date
> >>
> >> Range("A6:I2500").Select
> >> Selection.ClearContents
> >> ARANAN = Worksheets("ARAMA").Cells(3, 7)
> >> Worksheets("ARAMA").Cells(3, 7).Activate
> >> If ARANAN = "" Then
> >> Range("A6:I2500").Select
> >> Selection.ClearContents
> >> Worksheets("ARAMA").Cells(4, 9).Value = ""
> >> Worksheets("ARAMA").Cells(3, 7).Activate
> >> 'Range("I2").Value = Time
> >> 'Range("I1").Value = Date
> >> Exit Sub
> >> End If
> >> With Worksheets("LISTE").Range("D2:D62500")
> >> Set C = .Find(ARANAN, LookIn:=xlValues)
> >> If Not C Is Nothing Then
> >> firstAddress = C.Address
> >> SAY = 0
> >> Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
> >> Range("I2").Value = Time
> >> Range("I1").Value = Date
> >>
> >> Do
> >> Set C = .FindNext(C)
> >>
> >> adres = C.Address
> >> adres = Right(adres, Len(adres) - 1)
> >> ky = InStr(1, adres, "$", 1)
> >> AD1 = Right(adres, Len(adres) - ky)
> >> Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
> >> Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
> >> Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
> >> Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
> >> Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
> >> Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
> >> Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
> >> Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
> >> Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
> >> Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
> >> Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
> >> Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
> >> Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
> >> SAY = SAY + 1
> >> Loop While Not C Is Nothing And C.Address <> firstAddress
> >> End If
> >> Worksheets("ARAMA").Cells(4, 9).Value = SAY
> >> End With
> >> Range("G3").Select
> >> End Sub
> >>
> >>
> >>
> >>
> >
> >
>
>

Tom, thank you for your comments.
J_J

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> If the code is applying and removing the filter, extensibly outside the
> purview of the user, I am not sure what bearing the user's skill set has
> on
> the code.
>
> --
> Regards,
> Tom Ogilvy
>
> "J_J" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi Tom,
>> I do not want to use an advanced filter or an autofilter although it
> appears
>> to be the easiest way out. Becuse the user of this workbook will be a
>> newbee.
>> The records are in sheet called LISTE as shown in the code, and all I
>> need
>> to do is to check the dates (excluding empty cells) column there
>> regarding
>> the date I'll enter on sheet ARAMA, and display the records on sheet
>> ARAMA
>> with the same or newer dates. Hope I did explain what I intend to do...
>> Regards
>> J_J
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Think you would have to just loop through your column of dates and test
>> > each
>> > one.
>> >
>> > Hard to tell what you are doing - another possibility might be to use
>> > an
>> > advanced filter or an autofilter.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "J_J" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >> The below given code accepts a date (in "dd.mm.yyyy" format) entered
> into
>> >> the cell G3 of worksheet
>> >> "ARAMA" and then displays the records from the worksheet named "LISTE"
>> > that
>> >> matches with this criteria.
>> >> I need to modify it so that when I enter a date into Sheet "ARAMA" G3,
> I
>> >> want to display all records that has a date equal to or greater
>> >> (newer)
>> > then
>> >> that date. Can experts here comment on modifying my code?
>> >> TIA
>> >>
>> >> '--------------------------------
>> >> Public Sub Aratarih()
>> >>
>> >> Range("I2").Value = Time
>> >> Range("I1").Value = Date
>> >>
>> >> Range("A6:I2500").Select
>> >> Selection.ClearContents
>> >> ARANAN = Worksheets("ARAMA").Cells(3, 7)
>> >> Worksheets("ARAMA").Cells(3, 7).Activate
>> >> If ARANAN = "" Then
>> >> Range("A6:I2500").Select
>> >> Selection.ClearContents
>> >> Worksheets("ARAMA").Cells(4, 9).Value = ""
>> >> Worksheets("ARAMA").Cells(3, 7).Activate
>> >> 'Range("I2").Value = Time
>> >> 'Range("I1").Value = Date
>> >> Exit Sub
>> >> End If
>> >> With Worksheets("LISTE").Range("D2:D62500")
>> >> Set C = .Find(ARANAN, LookIn:=xlValues)
>> >> If Not C Is Nothing Then
>> >> firstAddress = C.Address
>> >> SAY = 0
>> >> Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
>> >> Range("I2").Value = Time
>> >> Range("I1").Value = Date
>> >>
>> >> Do
>> >> Set C = .FindNext(C)
>> >>
>> >> adres = C.Address
>> >> adres = Right(adres, Len(adres) - 1)
>> >> ky = InStr(1, adres, "$", 1)
>> >> AD1 = Right(adres, Len(adres) - ky)
>> >> Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
>> >> Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
>> >> Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
>> >> Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
>> >> Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
>> >> Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
>> >> Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
>> >> Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
>> >> Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
>> >> Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
>> >> Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
>> >> Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
>> >> Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
>> >> SAY = SAY + 1
>> >> Loop While Not C Is Nothing And C.Address <> firstAddress
>> >> End If
>> >> Worksheets("ARAMA").Cells(4, 9).Value = SAY
>> >> End With
>> >> Range("G3").Select
>> >> End Sub
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

follow-up question for similar problem....any preference/benefits between
Advancefilter and Autofilter to reduce a running balance sheet covering
several years to only those rows within the prior month when end user is NOT
involved??

The functions seem todo the same thing, so guessing there is benefit of one
over other but havent found any comparisons on/offline.
Thanks.

<snip>
'ensure all rows have a date
finalrow = Cells(Rows.Count, 7).End(xlUp).Row
With Range("B1:B" & finalrow)
..SpecialCells(xlCellTypeBlanks).Value = 0
..NumberFormat = "mm-dd-yy"
End With

'hide last month's data then delete all visible rows
BOM = "<" & DateSerial(Year(Now), Month(Now) - 1, 1)
EOM = ">" & DateSerial(Year(Now), Month(Now), 0)
With Range(Cells(1, 1), Cells(finalrow, 7))
..AutoFilter Field:=2, Criteria1:=BOM, Operator:=xlOr, Criteria2:=EOM
..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
..SpecialCells(xlCellTypeVisible).EntireRow.Delete
end with
activesheet.AutoFilterMode = False 'reveal remaining data
</snip>

"J_J" <[email protected]> wrote in message
news:#[email protected]...
> Tom, thank you for your comments.
> J_J
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > If the code is applying and removing the filter, extensibly outside the
> > purview of the user, I am not sure what bearing the user's skill set has
> > on
> > the code.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "J_J" <[email protected]> wrote in message
> > news:%[email protected]...
> >> Hi Tom,
> >> I do not want to use an advanced filter or an autofilter although it
> > appears
> >> to be the easiest way out. Becuse the user of this workbook will be a
> >> newbee.
> >> The records are in sheet called LISTE as shown in the code, and all I
> >> need
> >> to do is to check the dates (excluding empty cells) column there
> >> regarding
> >> the date I'll enter on sheet ARAMA, and display the records on sheet
> >> ARAMA
> >> with the same or newer dates. Hope I did explain what I intend to do...
> >> Regards
> >> J_J
> >>
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > Think you would have to just loop through your column of dates and
test
> >> > each
> >> > one.
> >> >
> >> > Hard to tell what you are doing - another possibility might be to use
> >> > an
> >> > advanced filter or an autofilter.
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> > "J_J" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi,
> >> >> The below given code accepts a date (in "dd.mm.yyyy" format) entered
> > into
> >> >> the cell G3 of worksheet
> >> >> "ARAMA" and then displays the records from the worksheet named
"LISTE"
> >> > that
> >> >> matches with this criteria.
> >> >> I need to modify it so that when I enter a date into Sheet "ARAMA"
G3,
> > I
> >> >> want to display all records that has a date equal to or greater
> >> >> (newer)
> >> > then
> >> >> that date. Can experts here comment on modifying my code?
> >> >> TIA
> >> >>
> >> >> '--------------------------------
> >> >> Public Sub Aratarih()
> >> >>
> >> >> Range("I2").Value = Time
> >> >> Range("I1").Value = Date
> >> >>
> >> >> Range("A6:I2500").Select
> >> >> Selection.ClearContents
> >> >> ARANAN = Worksheets("ARAMA").Cells(3, 7)
> >> >> Worksheets("ARAMA").Cells(3, 7).Activate
> >> >> If ARANAN = "" Then
> >> >> Range("A6:I2500").Select
> >> >> Selection.ClearContents
> >> >> Worksheets("ARAMA").Cells(4, 9).Value = ""
> >> >> Worksheets("ARAMA").Cells(3, 7).Activate
> >> >> 'Range("I2").Value = Time
> >> >> 'Range("I1").Value = Date
> >> >> Exit Sub
> >> >> End If
> >> >> With Worksheets("LISTE").Range("D2:D62500")
> >> >> Set C = .Find(ARANAN, LookIn:=xlValues)
> >> >> If Not C Is Nothing Then
> >> >> firstAddress = C.Address
> >> >> SAY = 0
> >> >> Worksheets("ARAMA").Cells(4, 9).Value = "ARANIYOR"
> >> >> Range("I2").Value = Time
> >> >> Range("I1").Value = Date
> >> >>
> >> >> Do
> >> >> Set C = .FindNext(C)
> >> >>
> >> >> adres = C.Address
> >> >> adres = Right(adres, Len(adres) - 1)
> >> >> ky = InStr(1, adres, "$", 1)
> >> >> AD1 = Right(adres, Len(adres) - ky)
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 1).Value = SAY + 1
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 2).Value =
> >> >> Trim(Worksheets("LISTE").Cells(AD1, 1).Value)
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 5).Value =
> >> >> Trim(Worksheets("LISTE").Cells(AD1, 2).Value)
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 6).Value =
> >> >> Trim(Worksheets("LISTE").Cells(AD1, 3).Value)
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 7).Value =
> >> >> Trim(Worksheets("LISTE").Cells(AD1, 4).Value)
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 8).Value =
> >> >> Trim(Worksheets("LISTE").Cells(AD1, 5).Value)
> >> >> Worksheets("ARAMA").Cells(6 + SAY, 9).Value =
> >> >> Trim(Worksheets("LISTE").Cells(AD1, 6).Value)
> >> >> SAY = SAY + 1
> >> >> Loop While Not C Is Nothing And C.Address <> firstAddress
> >> >> End If
> >> >> Worksheets("ARAMA").Cells(4, 9).Value = SAY
> >> >> End With
> >> >> Range("G3").Select
> >> >> End Sub
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Thank you Jef,
I'll try to adopt the idea into my situation.


"Jef Gorbach" <[email protected]> wrote in message
news:[email protected]...
> follow-up question for similar problem....any preference/benefits between
> Advancefilter and Autofilter to reduce a running balance sheet covering
> several years to only those rows within the prior month when end user is
> NOT
> involved??
>
> The functions seem todo the same thing, so guessing there is benefit of
> one
> over other but havent found any comparisons on/offline.
> Thanks.
>
> <snip>
> 'ensure all rows have a date
> finalrow = Cells(Rows.Count, 7).End(xlUp).Row
> With Range("B1:B" & finalrow)
> .SpecialCells(xlCellTypeBlanks).Value = 0
> .NumberFormat = "mm-dd-yy"
> End With
>
> 'hide last month's data then delete all visible rows
> BOM = "<" & DateSerial(Year(Now), Month(Now) - 1, 1)
> EOM = ">" & DateSerial(Year(Now), Month(Now), 0)
> With Range(Cells(1, 1), Cells(finalrow, 7))
> .AutoFilter Field:=2, Criteria1:=BOM, Operator:=xlOr, Criteria2:=EOM
> .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
> .SpecialCells(xlCellTypeVisible).EntireRow.Delete
> end with
> activesheet.AutoFilterMode = False 'reveal remaining data
> </snip>