re : Finding the datas and deleting datas which are not found.

Hi,

I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
which the number stand for the day of the month. Let say, i would like to
day number 5 and delete the rest of the rows which does not contain the day
number 5,
how do I go abt i writing the code for this?? Possible??

Thanks
Tony

Hi Ddiicc,

One way:

'========================>>
Public Sub Tester()
Dim rng As Range
Dim rCell As Range
Dim delRng As Range
Dim WB As Workbook
Dim SH As Worksheet
Dim CalcMode As Long

Set WB = ActiveWorkbook '<<========== CHANGE
Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
Set rng = Intersect(SH.UsedRange, Columns("AZ:AZ"))

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For Each rCell In rng.Cells
If rCell.Value <> 5 Then
If delRng Is Nothing Then
Set delRng = rCell
Else
Set delRng = Union(rCell, delRng)
End If
End If
Next rCell

If Not delRng Is Nothing Then
delRng.EntireRow.Delete
Else
'nothing found, do nothing
End If

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub
'<<========================

Alternatively, look at using the autofilter feature.

---
Regards,
Norman



"ddiicc" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
> which the number stand for the day of the month. Let say, i would like to
> day number 5 and delete the rest of the rows which does not contain the
> day
> number 5,
> how do I go abt i writing the code for this?? Possible??
>
> Thanks
> Tony

Simply apply in your code

Rows(dayfrom +1 & ":31").EntireRow.Delete

where dayfrom is 5 in your example.

Regards,
Stefi

„ddiicc” ezt �*rta:

> Hi,
>
> I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
> which the number stand for the day of the month. Let say, i would like to
> day number 5 and delete the rest of the rows which does not contain the day
> number 5,
> how do I go abt i writing the code for this?? Possible??
>
> Thanks
> Tony

Hi Stefi,
So If I want only day number 5 in the column (A:A) to stay and the rest
from 1 to 31 except for 5 to be deleted,

I add accordingly like this???

Row(5+1 & ":31.EntireRow.Delete???


"Stefi" wrote:

> Simply apply in your code
>
> Rows(dayfrom +1 & ":31").EntireRow.Delete
>
> where dayfrom is 5 in your example.
>
> Regards,
> Stefi
>
> „ddiicc” ezt �*rta:
>
> > Hi,
> >
> > I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
> > which the number stand for the day of the month. Let say, i would like to
> > day number 5 and delete the rest of the rows which does not contain the day
> > number 5,
> > how do I go abt i writing the code for this?? Possible??
> >
> > Thanks
> > Tony

Hi Norman,

My workname is Testers
Worksheet name is ALL32F_30580.

So therefore i should put as below >?????

Set WB = Testers
Set SH = WB.Sheets("ALL32F_30580")


"Norman Jones" wrote:

> Hi Ddiicc,
>
> One way:
>
> '========================>>
> Public Sub Tester()
> Dim rng As Range
> Dim rCell As Range
> Dim delRng As Range
> Dim WB As Workbook
> Dim SH As Worksheet
> Dim CalcMode As Long
>
> Set WB = ActiveWorkbook '<<========== CHANGE
> Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
> Set rng = Intersect(SH.UsedRange, Columns("AZ:AZ"))
>
> With Application
> CalcMode = .Calculation
> .Calculation = xlCalculationManual
> .ScreenUpdating = False
> End With
>
> For Each rCell In rng.Cells
> If rCell.Value <> 5 Then
> If delRng Is Nothing Then
> Set delRng = rCell
> Else
> Set delRng = Union(rCell, delRng)
> End If
> End If
> Next rCell
>
> If Not delRng Is Nothing Then
> delRng.EntireRow.Delete
> Else
> 'nothing found, do nothing
> End If
>
> With Application
> .Calculation = CalcMode
> .ScreenUpdating = True
> End With
>
> End Sub
> '<<========================
>
> Alternatively, look at using the autofilter feature.
>
> ---
> Regards,
> Norman
>
>
>
> "ddiicc" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
> > which the number stand for the day of the month. Let say, i would like to
> > day number 5 and delete the rest of the rows which does not contain the
> > day
> > number 5,
> > how do I go abt i writing the code for this?? Possible??
> >
> > Thanks
> > Tony
>
>
>

Hi Tony,


> My workname is Testers
> Worksheet name is ALL32F_30580.
>
> So therefore i should put as below >?????
>
> Set WB = Testers
> Set SH = WB.Sheets("ALL32F_30580")

Try:

Set WB =Workbooks("Testers" )
Set SH = WB.Sheets("ALL32F_30580")


---
Regards,
Norman



"ddiicc" <[email protected]> wrote in message
news:[email protected]...
> Hi Norman,
>
> My workname is Testers
> Worksheet name is ALL32F_30580.
>
> So therefore i should put as below >?????
>
> Set WB = Testers
> Set SH = WB.Sheets("ALL32F_30580")
>
>
> "Norman Jones" wrote:
>
>> Hi Ddiicc,
>>
>> One way:
>>
>> '========================>>
>> Public Sub Tester()
>> Dim rng As Range
>> Dim rCell As Range
>> Dim delRng As Range
>> Dim WB As Workbook
>> Dim SH As Worksheet
>> Dim CalcMode As Long
>>
>> Set WB = ActiveWorkbook '<<========== CHANGE
>> Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
>> Set rng = Intersect(SH.UsedRange, Columns("AZ:AZ"))
>>
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> For Each rCell In rng.Cells
>> If rCell.Value <> 5 Then
>> If delRng Is Nothing Then
>> Set delRng = rCell
>> Else
>> Set delRng = Union(rCell, delRng)
>> End If
>> End If
>> Next rCell
>>
>> If Not delRng Is Nothing Then
>> delRng.EntireRow.Delete
>> Else
>> 'nothing found, do nothing
>> End If
>>
>> With Application
>> .Calculation = CalcMode
>> .ScreenUpdating = True
>> End With
>>
>> End Sub
>> '<<========================
>>
>> Alternatively, look at using the autofilter feature.
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "ddiicc" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi,
>> >
>> > I have a column (AZ:AZ) header name "DAY", this column consist of 1 to
>> > 31
>> > which the number stand for the day of the month. Let say, i would like
>> > to
>> > day number 5 and delete the rest of the rows which does not contain the
>> > day
>> > number 5,
>> > how do I go abt i writing the code for this?? Possible??
>> >
>> > Thanks
>> > Tony
>>
>>
>>

To delete all except one:
dayexcept = 5
Rows("1:" & dayexcept - 1).EntireRow.Delete
Rows(dayexcept + 1 & ":31").EntireRow.Delete

If you don't use a variable for the day number, you can write simply to
delete all rows except row 5
Row("1:4").EntireRow.Delete
Row("6:31").EntireRow.Delete

Regards,
Stefi


„ddiicc” ezt �*rta:

> Hi Stefi,
> So If I want only day number 5 in the column (A:A) to stay and the rest
> from 1 to 31 except for 5 to be deleted,
>
> I add accordingly like this???
>
> Row(5+1 & ":31.EntireRow.Delete???
>
>
> "Stefi" wrote:
>
> > Simply apply in your code
> >
> > Rows(dayfrom +1 & ":31").EntireRow.Delete
> >
> > where dayfrom is 5 in your example.
> >
> > Regards,
> > Stefi
> >
> > „ddiicc” ezt �*rta:
> >
> > > Hi,
> > >
> > > I have a column (AZ:AZ) header name "DAY", this column consist of 1 to 31
> > > which the number stand for the day of the month. Let say, i would like to
> > > day number 5 and delete the rest of the rows which does not contain the day
> > > number 5,
> > > how do I go abt i writing the code for this?? Possible??
> > >
> > > Thanks
> > > Tony

Norman,
Many thanks to you (^,^)

"Norman Jones" wrote:

> Hi Tony,
>
>
> > My workname is Testers
> > Worksheet name is ALL32F_30580.
> >
> > So therefore i should put as below >?????
> >
> > Set WB = Testers
> > Set SH = WB.Sheets("ALL32F_30580")
>
> Try:
>
> Set WB =Workbooks("Testers" )
> Set SH = WB.Sheets("ALL32F_30580")
>
>
> ---
> Regards,
> Norman
>
>
>
> "ddiicc" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Norman,
> >
> > My workname is Testers
> > Worksheet name is ALL32F_30580.
> >
> > So therefore i should put as below >?????
> >
> > Set WB = Testers
> > Set SH = WB.Sheets("ALL32F_30580")
> >
> >
> > "Norman Jones" wrote:
> >
> >> Hi Ddiicc,
> >>
> >> One way:
> >>
> >> '========================>>
> >> Public Sub Tester()
> >> Dim rng As Range
> >> Dim rCell As Range
> >> Dim delRng As Range
> >> Dim WB As Workbook
> >> Dim SH As Worksheet
> >> Dim CalcMode As Long
> >>
> >> Set WB = ActiveWorkbook '<<========== CHANGE
> >> Set SH = WB.Sheets("Sheet1") '<<========== CHANGE
> >> Set rng = Intersect(SH.UsedRange, Columns("AZ:AZ"))
> >>
> >> With Application
> >> CalcMode = .Calculation
> >> .Calculation = xlCalculationManual
> >> .ScreenUpdating = False
> >> End With
> >>
> >> For Each rCell In rng.Cells
> >> If rCell.Value <> 5 Then
> >> If delRng Is Nothing Then
> >> Set delRng = rCell
> >> Else
> >> Set delRng = Union(rCell, delRng)
> >> End If
> >> End If
> >> Next rCell
> >>
> >> If Not delRng Is Nothing Then
> >> delRng.EntireRow.Delete
> >> Else
> >> 'nothing found, do nothing
> >> End If
> >>
> >> With Application
> >> .Calculation = CalcMode
> >> .ScreenUpdating = True
> >> End With
> >>
> >> End Sub
> >> '<<========================
> >>
> >> Alternatively, look at using the autofilter feature.
> >>
> >> ---
> >> Regards,
> >> Norman
> >>
> >>
> >>
> >> "ddiicc" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi,
> >> >
> >> > I have a column (AZ:AZ) header name "DAY", this column consist of 1 to
> >> > 31
> >> > which the number stand for the day of the month. Let say, i would like
> >> > to
> >> > day number 5 and delete the rest of the rows which does not contain the
> >> > day
> >> > number 5,
> >> > how do I go abt i writing the code for this?? Possible??
> >> >
> >> > Thanks
> >> > Tony
> >>
> >>
> >>
>
>
>