Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows if cell in row contains "a" or "o" or empty
I want to delete the entire rows if a cell in column F contains "a" or
"o" or empty. Then I also want to delete the rows which are in a range of row 10 to 25. I already made something but I cannot manage to only look in the range row 10 to 25. Sub Example() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "F").Value) Then ElseIf .Cells(Lrow, "F").Value = "a" Or .Cells(Lrow, "F").Value = "o" Or .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows if cell in row contains "a" or "o" or empty
Sub Example()
Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False .Rows("10:25").Delete StartRow = 1 EndRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Lrow = EndRow To StartRow Step -1 If not IsError(.Cells(Lrow, "F").Value) Then .Cells(Lrow, "F").Value = "a" Or _ .Cells(Lrow,"F").Value = "o" Or _ .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub "bartman1980" wrote: I want to delete the entire rows if a cell in column F contains "a" or "o" or empty. Then I also want to delete the rows which are in a range of row 10 to 25. I already made something but I cannot manage to only look in the range row 10 to 25. Sub Example() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "F").Value) Then ElseIf .Cells(Lrow, "F").Value = "a" Or .Cells(Lrow, "F").Value = "o" Or .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete rows if cell in row contains "a" or "o" or empty
On 31 okt, 17:08, Joel wrote:
Sub Example() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False .Rows("10:25").Delete StartRow = 1 EndRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Lrow = EndRow To StartRow Step -1 If not IsError(.Cells(Lrow, "F").Value) Then .Cells(Lrow, "F").Value = "a" Or _ .Cells(Lrow,"F").Value = "o" Or _ .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub "bartman1980" wrote: I want to delete the entire rows if a cell in column F contains "a" or "o" or empty. Then I also want to delete the rows which are in a range of row 10 to 25. I already made something but I cannot manage to only look in the range row 10 to 25. Sub Example() Dim Lrow As Long Dim CalcMode As Long Dim StartRow As Long Dim EndRow As Long With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .DisplayPageBreaks = False StartRow = 1 EndRow = .Cells(.Rows.Count, "F").End(xlUp).Row For Lrow = EndRow To StartRow Step -1 If IsError(.Cells(Lrow, "F").Value) Then ElseIf .Cells(Lrow, "F").Value = "a" Or .Cells(Lrow, "F").Value = "o" Or .Cells(Lrow, "F").Value = "" Then .Rows(Lrow).Delete End If Next End With With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - I ment to delete only the rows at rows 10 to 25 IF the letters a or o or nothing are in column F |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Look for cell containing "Initial" then if the next cell after equals "Final" then delete both rows. | Excel Programming | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |