| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete rows if value matches
Having some success but stuck with checking values in several columns. The
below was taken from Ron de Bruin's web site (which is great I must add) but I can't get the code to delete rows where the criteria matches. Also, once sorted, is there an option to do the same but us OR instead of AND eg. if contents of cell column A = ron, delete or if contents of cell in column B = dave, delete and so on. Thanks, Rob http://www.rondebruin.nl/delete.htm#Loop 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Cells(Lrow, "A").Value = "ron" And _ .Cells(Lrow, "B").Value = "dave" And _ .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete 'If .Value = "ron" Then .EntireRow.Delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows if value matches
Remember that VBA's comparisons are case sensitive: Dave < dave < DaVe
And if you want or's If lcase(.Cells(Lrow, "A").Value) = lcase("ron") _ or lcase(.Cells(Lrow, "B").Value) = lcase("dave") _ or .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete if you want a mixture of and's and or's, it'll make your code easier to read if you surround the stuff that goes together with ()'s. If (lcase(.Cells(Lrow, "A").Value) = lcase("ron") _ and lcase(.Cells(Lrow, "B").Value) = lcase("dave")) _ or .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete Column A has to be Ron and at the same time column B has to be Dave. Or column C has to be 10. If either are true, then delete the row. Rob wrote: Having some success but stuck with checking values in several columns. The below was taken from Ron de Bruin's web site (which is great I must add) but I can't get the code to delete rows where the criteria matches. Also, once sorted, is there an option to do the same but us OR instead of AND eg. if contents of cell column A = ron, delete or if contents of cell in column B = dave, delete and so on. Thanks, Rob http://www.rondebruin.nl/delete.htm#Loop 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Cells(Lrow, "A").Value = "ron" And _ .Cells(Lrow, "B").Value = "dave" And _ .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete 'If .Value = "ron" Then .EntireRow.Delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows if value matches
See the examples below the macro Rob about case
http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Having some success but stuck with checking values in several columns. The below was taken from Ron de Bruin's web site (which is great I must add) but I can't get the code to delete rows where the criteria matches. Also, once sorted, is there an option to do the same but us OR instead of AND eg. if contents of cell column A = ron, delete or if contents of cell in column B = dave, delete and so on. Thanks, Rob http://www.rondebruin.nl/delete.htm#Loop 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Cells(Lrow, "A").Value = "ron" And _ .Cells(Lrow, "B").Value = "dave" And _ .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete 'If .Value = "ron" Then .EntireRow.Delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows if value matches
Thanks, Case was my issue and not replacing all the code, thought you had to
retain If Not IsError(.Value) Then.... Regards, Rob "Ron de Bruin" wrote in message ... See the examples below the macro Rob about case http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Having some success but stuck with checking values in several columns. The below was taken from Ron de Bruin's web site (which is great I must add) but I can't get the code to delete rows where the criteria matches. Also, once sorted, is there an option to do the same but us OR instead of AND eg. if contents of cell column A = ron, delete or if contents of cell in column B = dave, delete and so on. Thanks, Rob http://www.rondebruin.nl/delete.htm#Loop 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Cells(Lrow, "A").Value = "ron" And _ .Cells(Lrow, "B").Value = "dave" And _ .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete 'If .Value = "ron" Then .EntireRow.Delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete rows if value matches
If you always want to use a text comparison (ignore case) for code in that
module, you can add: Option Compare Text at the top of the module. Rob wrote: Thanks, Case was my issue and not replacing all the code, thought you had to retain If Not IsError(.Value) Then.... Regards, Rob "Ron de Bruin" wrote in message ... See the examples below the macro Rob about case http://www.rondebruin.nl/delete.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Rob" wrote in message ... Having some success but stuck with checking values in several columns. The below was taken from Ron de Bruin's web site (which is great I must add) but I can't get the code to delete rows where the criteria matches. Also, once sorted, is there an option to do the same but us OR instead of AND eg. if contents of cell column A = ron, delete or if contents of cell in column B = dave, delete and so on. Thanks, Rob http://www.rondebruin.nl/delete.htm#Loop 'Set the first and last row to loop through Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row 'We loop from Lastrow to Firstrow (bottom to top) For Lrow = Lastrow To Firstrow Step -1 'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Cells(Lrow, "A").Value = "ron" And _ .Cells(Lrow, "B").Value = "dave" And _ .Cells(Lrow, "C").Value 10 Then .Rows(Lrow).Delete 'If .Value = "ron" Then .EntireRow.Delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With Next Lrow -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| compare rows for a match and sum the matches | Excel Worksheet Functions | |||
| Returning matches from mutiple rows | Excel Worksheet Functions | |||
| How can I delete all matches in a workbook? | Excel Worksheet Functions | |||
| Find 2 Matches Wors Then Cut Selected Rows | Excel Programming | |||
| Calling all MVPs! Macro to delete non matches b/t two lists. Thank | Excel Programming | |||
Linear Mode
