Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I strike through a row of datas? | Excel Discussion (Misc queries) | |||
Counting datas in one range based on datas in another range | Excel Discussion (Misc queries) | |||
A chart with 2 datas | Charts and Charting in Excel | |||
how to delete datas without deleting formulas | Excel Discussion (Misc queries) | |||
Datas from Access | Excel Discussion (Misc queries) |