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

Finding the datas and deleting datas which are not found.
 

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" wrote in message
...
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

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

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

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

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

Finding the datas and deleting datas which are not found.
 

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" wrote in message
...
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

Finding the datas and deleting datas which are not found.
 

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" wrote in message
...
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" wrote in message
...
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

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

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

Finding the datas and deleting datas which are not found.
 

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" wrote in message
...
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" wrote in message
...
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