Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for cell containing "Initial" then if the next cell after equals "Final" then delete both rows.
Hi everyone
I pretty much summed everything up above. Thousands of rows of data most of which is useless but I thought the best way to delete would be to find in column A the word "Initial" and if the cell immediately after is "Final" delete both rows. If not then this is a good row and it should continue until it finds the next occurrence of "Initial" I hope I make sense, Thanks alot in advance, Andrea |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for cell containing "Initial" then if the next cell after equ
Maybe something like this. It goes bottom to top and looks for Final first.
If the cell above it says "Initial" then both rows are deleted. Sub test() Dim lRow As Long Dim l As Long lRow = Range("A65536").End(xlUp).Row For l = lRow To 2 Step -1 If Range("A" & l).Value = "Final" And Range("A" & l - 1).Value = "Initial" Then Range(Range("A" & l), Range("A" & l - 1)).EntireRow.Delete l = l - 1 End If Next l End Sub -- Hope that helps. Vergel Adriano " wrote: Hi everyone I pretty much summed everything up above. Thousands of rows of data most of which is useless but I thought the best way to delete would be to find in column A the word "Initial" and if the cell immediately after is "Final" delete both rows. If not then this is a good row and it should continue until it finds the next occurrence of "Initial" I hope I make sense, Thanks alot in advance, Andrea |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for cell containing "Initial" then if the next cell after equ
Sub Macro1()
Dim rng as Range, r as Range Dim sAddr as String Set rng = Columns(1).Find(What:="B", _ After:=Range("A65536"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) if not rng is nothing then sAddr = rng.Address do if rng.offset(1,0) = "Final" then if r is nothing then set r = rng.Resize(2,1) else set r = Union(r,rng.Resize(2,1) end if end if set rng = columns(1).FindNext(rng) Loop while rng.Address < sAddr if not r is nothing then r.EntireRow.Delete End if end Sub -- Regards, Tom Ogilvy " wrote: Hi everyone I pretty much summed everything up above. Thousands of rows of data most of which is useless but I thought the best way to delete would be to find in column A the word "Initial" and if the cell immediately after is "Final" delete both rows. If not then this is a good row and it should continue until it finds the next occurrence of "Initial" I hope I make sense, Thanks alot in advance, Andrea |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Look for cell containing "Initial" then if the next cell afterequals "Final" then delete both rows.
One way is to just start at the bottom and go to the top and look for that
matching pair: Option Explicit Sub testme01() Dim wks As Worksheet Dim iRow As Long Dim FirstRow As Long Dim LastRow As Long Dim DelRng As Range Set wks = Worksheets("sheet1") With wks FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow Step -1 If LCase(.Cells(iRow, "A").Value) = LCase("initial") _ And LCase(.Cells(iRow + 1, "A").Value) = LCase("final") Then If DelRng Is Nothing Then Set DelRng = .Cells(iRow, "A").Resize(2, 1) Else Set DelRng = Union(DelRng, .Cells(iRow, "A").Resize(2, 1)) End If End If Next iRow End With If DelRng Is Nothing Then 'no matches Else '.Select to test and verify. .Delete to do the real deleting. DelRng.EntireRow.Select '.Delete End If End Sub " wrote: Hi everyone I pretty much summed everything up above. Thousands of rows of data most of which is useless but I thought the best way to delete would be to find in column A the word "Initial" and if the cell immediately after is "Final" delete both rows. If not then this is a good row and it should continue until it finds the next occurrence of "Initial" I hope I make sense, Thanks alot in advance, Andrea -- Dave Peterson |
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) | |||
"CELL("FILENAME") NOT UPDATE AFTER "SAVE AS" ACTION | Excel Discussion (Misc queries) | |||
How to get the final "result" to show in one cell? Excell 2002 | Excel Discussion (Misc queries) | |||
Help!!! Enter "7" in a cell and Excel changes the "7" to "11" immediately!!! | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) |