Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows on value
Hi...
When I use the code below it doesn't work. I want to delete any row where the an N is found in column C. Can anyone fix this for me. The problem is that it only deletes a few at a time and I don't know why. Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If UCase(Cells(r, 3).Value) = "N" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows on value
Assumng the code is in a general/standardd module, the code looks good. If
it isn't deleting the rows, then there is more than the value N in the missed rows. Perhaps using Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Instr(1,Cells(r, 3).Value,"N",vbTextcompare) Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub or use Ucase(trim(cells(r,3)) in your original code it the culprit could be spaces. the correct answer depends on what is in the cells - knowledge only available to you. -- Regards, Tom Ogilvy "Gordon" wrote: Hi... When I use the code below it doesn't work. I want to delete any row where the an N is found in column C. Can anyone fix this for me. The problem is that it only deletes a few at a time and I don't know why. Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If UCase(Cells(r, 3).Value) = "N" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows on value
Hi Tom...
The cells in column c contain either an N or a zero (0). I tried that code but it didn't stack up and just deleted the top line... Any help would be fab... Gordon... "Tom Ogilvy" wrote: Assumng the code is in a general/standardd module, the code looks good. If it isn't deleting the rows, then there is more than the value N in the missed rows. Perhaps using Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Instr(1,Cells(r, 3).Value,"N",vbTextcompare) Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub or use Ucase(trim(cells(r,3)) in your original code it the culprit could be spaces. the correct answer depends on what is in the cells - knowledge only available to you. -- Regards, Tom Ogilvy "Gordon" wrote: Hi... When I use the code below it doesn't work. I want to delete any row where the an N is found in column C. Can anyone fix this for me. The problem is that it only deletes a few at a time and I don't know why. Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If UCase(Cells(r, 3).Value) = "N" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows on value
In contrast, I ran that code on a worksheet with "either an N or a zero (0)"
in column C and it worked flawlessly. I don't think it is the code that doesn't stack up. Thus, I don't think there is any help I can offer, since as I said, it isn't the code (which works exactly as expected) and that is all that is on the table here. -- Regards, Tom Ogilvy "Gordon" wrote in message ... Hi Tom... The cells in column c contain either an N or a zero (0). I tried that code but it didn't stack up and just deleted the top line... Any help would be fab... Gordon... "Tom Ogilvy" wrote: Assumng the code is in a general/standardd module, the code looks good. If it isn't deleting the rows, then there is more than the value N in the missed rows. Perhaps using Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If Instr(1,Cells(r, 3).Value,"N",vbTextcompare) Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub or use Ucase(trim(cells(r,3)) in your original code it the culprit could be spaces. the correct answer depends on what is in the cells - knowledge only available to you. -- Regards, Tom Ogilvy "Gordon" wrote: Hi... When I use the code below it doesn't work. I want to delete any row where the an N is found in column C. Can anyone fix this for me. The problem is that it only deletes a few at a time and I don't know why. Sub Delete_N_MarkedRows() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim lastrow As Long, r As Long lastrow = ActiveSheet.UsedRange.Rows.Count For r = lastrow To 1 Step -1 If UCase(Cells(r, 3).Value) = "N" Then Rows(r).Delete Next r Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hpw do I delete multiple empty rows found between filled rows? | Excel Worksheet Functions | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Delete rows with numeric values, leave rows with text | Excel Programming | |||
How to delete rows when List toolbar's "delete" isnt highlighted? | Excel Worksheet Functions | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |