PDA

View Full Version : Solved: macro to remove data from list



Pete
11-07-2008, 02:33 AM
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

xld
11-07-2008, 02:55 AM
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
11-07-2008, 02:57 AM
thanks xld......works prefectly...

xld
11-07-2008, 02:58 AM
I think that is the first time I have actually been able to help you :-)

david000
11-07-2008, 08:35 PM
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?

xld
11-08-2008, 03:05 AM
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.