Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
check range for certain value
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
|
|||
|
|||
check range for certain value
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
|
|||
|
|||
check range for certain value
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
|
|||
|
|||
check range for certain value
Ah. I see. Try this instead:
Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCounter As Integer Dim strConcatenate As String '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 strConcatenate = strConcatenate & _ Trim(Cells(lngRow, intCounter).Value) Next intCounter If strConcatenate = "MS" Then Rows(lngRow).EntireRow.Delete End If strConcatenate = "" Next lngRow End Sub -- HTH, Dianne In , Gareth typed: 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
|
|||
|
|||
check range for certain value
Just for information, Gareth started a new thread on this.
-- Regards, Tom Ogilvy "Dianne" wrote in message ... Ah. I see. Try this instead: Sub CheckInitials() Dim lngRow As Long Dim lngLastRow As Long Dim intCounter As Integer Dim strConcatenate As String '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 strConcatenate = strConcatenate & _ Trim(Cells(lngRow, intCounter).Value) Next intCounter If strConcatenate = "MS" Then Rows(lngRow).EntireRow.Delete End If strConcatenate = "" Next lngRow End Sub -- HTH, Dianne In , Gareth typed: 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 | |||
Check for empty range in vba | Excel Discussion (Misc queries) | |||
Q: check a range values | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
How to check cells in a range for a value | Excel Programming | |||
check if range containing values | Excel Programming |