Seek and destroy

  • Thread starter Thread starter jancairns
  • Start date Start date
J

jancairns

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
 
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
 
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
 
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.
 
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
 
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
 
Yes, you typed in x1up rather than xlup. the constant is all letters, no
numbers.
 
Yes, you typed in x1up rather than xlup. the constant is all letters, no
numbers.
 
Back
Top