Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
Firstly, apologies for asking the same question again but I was afraid that
I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
Try something like this
Private Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1 If Application.CountIf(Range(Cells(r, "P"), Cells(r, "T")), "MS") 0 Then .Rows(r).Delete End If Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... Firstly, apologies for asking the same question again but I was afraid that I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
For lngRow = lngLastRow To 1 Step -1 ' check that only one cell in columns P to T contains a value if Application.CountA(cells(lngRow,16).Resize(1,4)) = 1 then For intCounter = 16 To 20 ' check P to T ' check if singe cell with value contains MS and only MS If Trim(Cells(lngRow, intCounter).Value) = "MS" Then ' a row with MS and only MS in P to T Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter End If Next lngRow -- Regards, Tom Ogilvy Gareth wrote in message ... Firstly, apologies for asking the same question again but I was afraid that I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
That would still delete a row that had other codes in addition to MS.
Maybe with the added code: Private Sub Test() Dim r As Long Dim r1 as Range Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1 set r1 = .Range(.Cells(r, "P"), .Cells(r, "T")) If Application.CountIf(r1,"MS") =1 and Application.CountA(r1) = 1 Then .Rows(r).Delete End If Next End With Application.ScreenUpdating = True End Sub If a row with multiple MS codes is to be deleted, then change =1 to 1 for the first condition. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Try something like this Private Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1 If Application.CountIf(Range(Cells(r, "P"), Cells(r, "T")), "MS") 0 Then .Rows(r).Delete End If Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... Firstly, apologies for asking the same question again but I was afraid that I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
Oops
I misread -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Tom Ogilvy" wrote in message ... That would still delete a row that had other codes in addition to MS. Maybe with the added code: Private Sub Test() Dim r As Long Dim r1 as Range Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1 set r1 = .Range(.Cells(r, "P"), .Cells(r, "T")) If Application.CountIf(r1,"MS") =1 and Application.CountA(r1) = 1 Then .Rows(r).Delete End If Next End With Application.ScreenUpdating = True End Sub If a row with multiple MS codes is to be deleted, then change =1 to 1 for the first condition. -- Regards, Tom Ogilvy Ron de Bruin wrote in message ... Try something like this Private Sub Test() Dim r As Long Application.ScreenUpdating = False With Worksheets("Sheet1") For r = .UsedRange.Rows(.UsedRange.Rows.Count).Row To 1 Step -1 If Application.CountIf(Range(Cells(r, "P"), Cells(r, "T")), "MS") 0 Then .Rows(r).Delete End If Next End With Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Gareth" wrote in message ... Firstly, apologies for asking the same question again but I was afraid that I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
Tom
Many thanks, just the job..... I have remembered something else though.... The sheet is made up of duplicated rows of data, the only difference between the rows being the addition of any codes in P:T. Your code deletes any MS only rows of data, but I need it to also delete the same row without the code. Both rows will have the same ID number in column A Is there a way to find MS only codes and then delete any rows with the same value in column A? PS There will be occasions when an MS code doesn't have a duplicate row. Gareth {clear as mud} "Tom Ogilvy" wrote in message ... For lngRow = lngLastRow To 1 Step -1 ' check that only one cell in columns P to T contains a value if Application.CountA(cells(lngRow,16).Resize(1,4)) = 1 then For intCounter = 16 To 20 ' check P to T ' check if singe cell with value contains MS and only MS If Trim(Cells(lngRow, intCounter).Value) = "MS" Then ' a row with MS and only MS in P to T Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter End If Next lngRow -- Regards, Tom Ogilvy Gareth wrote in message ... Firstly, apologies for asking the same question again but I was afraid that I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
repeat post
If the entry in column A matches the entry in column A for a row that was
deleted, it (the row) is deleted - no check on codes in P:T for the what codes they have (they would already have failed that test). That is what I understood you to want. Public Sub Test() Dim r As Long Dim r1 As Range Dim lrw As Long Dim list As Variant Dim rng As Range Application.ScreenUpdating = False With Worksheets("Sheet1") lrw = .UsedRange.Rows(.UsedRange.Rows.Count).Row ReDim list(1 To lrw) icnt = 0 For r = lrw To 1 Step -1 Set r1 = .Range(.Cells(r, "P"), .Cells(r, "T")) If Application.CountIf(r1, "MS") = 1 And _ Application.CountA(r1) = 1 Then icnt = icnt + 1 list(icnt) = .Cells(r, 1).Value .Rows(r).Delete End If Next ReDim Preserve list(1 To icnt) For i = 1 To icnt .Columns(1).Replace What:=list(i), _ Replacement:="=na()", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False Next On Error Resume Next Set rng = Columns(1).SpecialCells(xlFormulas, xlErrors) On Error GoTo 0 If Not rng Is Nothing Then rng.EntireRow.Delete End If End With Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy Gareth wrote in message ... Tom Many thanks, just the job..... I have remembered something else though.... The sheet is made up of duplicated rows of data, the only difference between the rows being the addition of any codes in P:T. Your code deletes any MS only rows of data, but I need it to also delete the same row without the code. Both rows will have the same ID number in column A Is there a way to find MS only codes and then delete any rows with the same value in column A? PS There will be occasions when an MS code doesn't have a duplicate row. Gareth {clear as mud} "Tom Ogilvy" wrote in message ... For lngRow = lngLastRow To 1 Step -1 ' check that only one cell in columns P to T contains a value if Application.CountA(cells(lngRow,16).Resize(1,4)) = 1 then For intCounter = 16 To 20 ' check P to T ' check if singe cell with value contains MS and only MS If Trim(Cells(lngRow, intCounter).Value) = "MS" Then ' a row with MS and only MS in P to T Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter End If Next lngRow -- Regards, Tom Ogilvy Gareth wrote in message ... Firstly, apologies for asking the same question again but I was afraid that I might of been forgotten. From the question below you will see that I onlty want to delete rows which have an MS in the range P:T, the code that Dianne suuplied deletes the row if MS exists with any other code. many thanks Gareth -------------------------------- Dianne Thanks for this but perhaps I didn't ask the question properly: I only want the row deleted if MS is the only entry in the range, your code deletes any row containing MS in the range. Help........ "Dianne" wrote in message ... Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCol As Integer Dim intCounter As Integer 'Here you will need to use a column 'that will ALWAYS contain a value -- 'at least in the very last row 'I have used A in my example lngLastRow = ActiveSheet.Range("A65536").End(xlUp).Row For lngRow = lngLastRow To 1 Step -1 For intCounter = 16 To 20 If Trim(Cells(lngRow, intCounter).Value) = "MS" Then Rows(lngRow).EntireRow.Delete Exit For End If Next intCounter Next lngRow End Sub -- HTH, Dianne In , Gareth typed: I want to check a range (P:T) in every row. These cells will contain 2 letter codes, if the code 'MS' is found ON ITS OWN then I want to delete that row. The number of rows on the sheet will vary. Examples: MS TG do not delete MS delete no codes at all do not delete BB DD FG TR do not delete Thanks in advance. Gareth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Should I generally request "post a poll" when I post a new thread? | Excel Discussion (Misc queries) | |||
how to repeat rows at the bottom while using the rows repeat at to | Setting up and Configuration of Excel | |||
Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec | Excel Discussion (Misc queries) | |||
My post does not go through (twice) | Excel Programming | |||
Re-Post Anyone know how to do this? | Excel Programming |