Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
I have an output sheet that I am trying to extract
information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Sub MoveStd()
Dim rng As Range, rng1 As Range, rng2 As Range Dim fAddr As String Set rng = Range(Cells(4, 2), Cells(Rows.Count, 2).End(xlUp)) Set rng1 = rng.Find(What:="STD", After:=rng(rng.Count), _ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then fAddr = rng1.Address Do If rng2 Is Nothing Then Set rng2 = rng1 Else Set rng2 = Union(rng1, rng2) End If Set rng1 = rng.FindNext(rng1) Loop While rng1.Address < fAddr ' rng2.EntireRow.Copy Destination:= _ ' Worksheets("Sheet2").Range("A1") rng2.EntireRow.Delete End If End Sub -- Regards, Tom Ogilvy " wrote in message ... I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Hi Jan,
try the following macro: Sub DeleteSTD() ActiveSheet.Cells(65536, 2).Select Selection.End(xlUp).Select LastRow = Selection.Row For i = 4 To LastRow If UCase(Cells(i, 2).Value) = "STD" Then Rows(i).Delete End If Next End Sub Regards Klaus -----Original Message----- I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Just a heads up:
If you have two or more adjacent rows that contain STD in them, then the macro suggested won't delete all the rows containing STD unless you run it repeatedly until all are gone. -- Regards, Tom Ogilvy "Klaus" wrote in message ... Hi Jan, try the following macro: Sub DeleteSTD() ActiveSheet.Cells(65536, 2).Select Selection.End(xlUp).Select LastRow = Selection.Row For i = 4 To LastRow If UCase(Cells(i, 2).Value) = "STD" Then Rows(i).Delete End If Next End Sub Regards Klaus -----Original Message----- I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Hi Tom
Macro is showing Run Tim Error 1004 - Application-Define or Object Define Error On going into Debug it is highlighting the following line: Set rng = Range(Cells(4, 2), Cells(Rows.Count, 2).End (x1up)) Have I done something wrong, I am just learning VB. Any idea where I have gone wrong. Regards Jan -----Original Message----- Sub MoveStd() Dim rng As Range, rng1 As Range, rng2 As Range Dim fAddr As String Set rng = Range(Cells(4, 2), Cells(Rows.Count, 2).End (xlUp)) Set rng1 = rng.Find(What:="STD", After:=rng(rng.Count), _ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then fAddr = rng1.Address Do If rng2 Is Nothing Then Set rng2 = rng1 Else Set rng2 = Union(rng1, rng2) End If Set rng1 = rng.FindNext(rng1) Loop While rng1.Address < fAddr ' rng2.EntireRow.Copy Destination:= _ ' Worksheets("Sheet2").Range("A1") rng2.EntireRow.Delete End If End Sub -- Regards, Tom Ogilvy " wrote in message ... I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Hi Klaus
Macro is showing Run Tim Error 1004 - Application-Define or Object Define Error On going into Debug it is highlighting the following line: Selection.End(x1Up).Select The cursor is also sitting on row 65536 (or any other row number I changed the macro to) on the worksheet Have I done something wrong, I am just learning VB. Any idea where I have gone wrong. Regards Jan -----Original Message----- Hi Jan, try the following macro: Sub DeleteSTD() ActiveSheet.Cells(65536, 2).Select Selection.End(xlUp).Select LastRow = Selection.Row For i = 4 To LastRow If UCase(Cells(i, 2).Value) = "STD" Then Rows(i).Delete End If Next End Sub Regards Klaus -----Original Message----- I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Yes, you typed in x1up rather than xlup. the constant is all letters, no
numbers. -- Regards, Tom Ogilvy "Jan Cairns" wrote in message ... Hi Tom Macro is showing Run Tim Error 1004 - Application-Define or Object Define Error On going into Debug it is highlighting the following line: Set rng = Range(Cells(4, 2), Cells(Rows.Count, 2).End (x1up)) Have I done something wrong, I am just learning VB. Any idea where I have gone wrong. Regards Jan -----Original Message----- Sub MoveStd() Dim rng As Range, rng1 As Range, rng2 As Range Dim fAddr As String Set rng = Range(Cells(4, 2), Cells(Rows.Count, 2).End (xlUp)) Set rng1 = rng.Find(What:="STD", After:=rng(rng.Count), _ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rng1 Is Nothing Then fAddr = rng1.Address Do If rng2 Is Nothing Then Set rng2 = rng1 Else Set rng2 = Union(rng1, rng2) End If Set rng1 = rng.FindNext(rng1) Loop While rng1.Address < fAddr ' rng2.EntireRow.Copy Destination:= _ ' Worksheets("Sheet2").Range("A1") rng2.EntireRow.Delete End If End Sub -- Regards, Tom Ogilvy " wrote in message ... I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seek and destroy
Yes, you typed in x1up rather than xlup. the constant is all letters, no
numbers. -- Regards, Tom Ogilvy "Jan Cairns" wrote in message ... Hi Klaus Macro is showing Run Tim Error 1004 - Application-Define or Object Define Error On going into Debug it is highlighting the following line: Selection.End(x1Up).Select The cursor is also sitting on row 65536 (or any other row number I changed the macro to) on the worksheet Have I done something wrong, I am just learning VB. Any idea where I have gone wrong. Regards Jan -----Original Message----- Hi Jan, try the following macro: Sub DeleteSTD() ActiveSheet.Cells(65536, 2).Select Selection.End(xlUp).Select LastRow = Selection.Row For i = 4 To LastRow If UCase(Cells(i, 2).Value) = "STD" Then Rows(i).Delete End If Next End Sub Regards Klaus -----Original Message----- I have an output sheet that I am trying to extract information from. Trouble is it is not always in the same rows or the same number of rows in every output (100 rows Max). I have identified a common factor in column B for all the rows that are unwanted they contain STD (in upper, lower or mixed case) as part of the cell contents. I need a macro that will search down column B (Cell B4 Start point) until it comes to a cell containing STD then for it to remove that entire row from the sheet2 and continue until all rows containing STD in column B are removed and I am left with only the required data. Any help on this would be most welcome. Rrgards Jan . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Goal Seek | Excel Discussion (Misc queries) | |||
Goal Seek | Excel Discussion (Misc queries) | |||
Goal seek - Is there a bug? | Excel Discussion (Misc queries) | |||
How to use goal seek | Excel Worksheet Functions | |||
Destroy command button with a Macro? | Excel Discussion (Misc queries) |