Solved: macro to remove data from list

[Pete]

see attached file
Hi

Need to amend the current macro to remove the data in worksheet"Sheet1" from row 851:903 columns B:F i.e remove guernsay from the data.

The end result being the same table as worksheet 2 minus Guernsay from the list

i have made one attempt but i feel something is missing from the following vba code...

Sub Edit_Guernsey()

Application.ScreenUpdating = False
Cells.Find(What:="Guernsey", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range("B851:F903").Select
Rows("851:903").Select
Selection.Delete Shift:=xlUp
Range("B851").Select

Application.ScreenUpdating = True

End Sub

[Bob Phillips]

Sub Edit_Guernsey()
Const TARGET_COUNTRY As String = "Guernsey"
Dim StartCell As Range
Dim EndCell As Range
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")

Set StartCell = .Cells.Find( _
What:=TARGET_COUNTRY, _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set EndCell = .Cells.Find( _
What:="TECHNOLOGY", _
After:=StartCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

i = EndCell.Row
Do
i = i + 1
Loop Until Not .Cells(i, "B").Value Like "*.*"
.Rows(StartCell.Row).Resize(i - StartCell.Row).Delete
End With

Application.ScreenUpdating = True

End Sub

[Pete]

thanks xld......works prefectly...

[Bob Phillips]

I think that is the first time I have actually been able to help you :-)

[david000]

Loop Until Not .Cells(i, "B").Value Like "*.*"


Bob!?

What is the Like function doing?

I've been thinking about it all day...Dosen't the Start find, and End find define the row parameters?

Is it there in case the loop fails?

[Bob Phillips]

No not quite, StartRow defines the start of the data it is true, but EndRow defines the start of the last block of data. We don#t know the name of the next country, and there is nothing special about the rows to allow us to pick it out. So what I did was to find the country in question (StartRow), then find the row with TECHNOLOGY after that row (EndRow), and then loop through until I found a row without a dot (.) in it, as TECHNOLOGY is followed by rows starting with 1., 2., etc. That is where Like kicks in, and I can loop until I get past those rows to the next country name.