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

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

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


.

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


.

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




.

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


.

.

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




.

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


.

.