Deleting rows above and below data required

[David Wale]

Hello, a sub routine required please.

I want to look down column A and find the first instance of either, GK, DF, MF or

ST, (in this example ST is the the first instance at A32, it could have been GK, DF

or MF).

When the first instance has been found I want to delete all the rows above the

first instance, so ST at A32 would now be at A1.

Also, looking down column A, I want to find the last instance of either GK, DF, MF

or ST and delete all the rows after the last instance, (in this example GK is the last

instance at A58).

Thank you for any anticipated assistance

David Wale

	A
79,700 Likes
50,703 Followers
Your Basket £0.00
Your Tokens (0)
Log in
The Sun £1 MILLION Dream team - Fantasy Football 2011/2012
Proud Partner William Hill
Home
Predictor
Playing & Prizes
Stat Zone
Fixtures
Bet
FAQ
All
Goalkeepers
Defenders
Midfielders
Strikers
Back to Stat Zone
Twitter
Facebook
www.thesun.co.uk
dreamteamfc
dreamteamfc
dreamteamfc
1. Most asked questions
2. How to play
3. Team Selection
4. Scoring
5. Transfers
6. Mini-leagues
7. Prizes
8. Packages
9. Payments
10. Lost details
11. How to play
PAYMENT POWERED BY
Contact us
T&C's
Privacy Policy
Payment Options
By continuing to use the site, you agree to the use of cookies. You can change this and find out more by following this link.

<COLGROUP> <COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"> </COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: left"]Player Stats[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: center"]Player Stats[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: left"]Select a player to view their profile[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: left"]Click on the preferred column header to sort the stats[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]31[/TD]
[TD="align: left"]POS.[/TD]

[TD="bgcolor: #cacaca, align: center"]32[/TD]
[TD="align: left"]ST[/TD]

[TD="bgcolor: #cacaca, align: center"]33[/TD]
[TD="bgcolor: #c0c0c0, align: left"]ST[/TD]

[TD="bgcolor: #cacaca, align: center"]34[/TD]
[TD="align: left"]ST[/TD]

[TD="bgcolor: #cacaca, align: center"]35[/TD]
[TD="bgcolor: #c0c0c0, align: left"]ST[/TD]

[TD="bgcolor: #cacaca, align: center"]36[/TD]
[TD="align: left"]ST[/TD]

[TD="bgcolor: #cacaca, align: center"]37[/TD]
[TD="bgcolor: #c0c0c0, align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]38[/TD]
[TD="align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]39[/TD]
[TD="bgcolor: #c0c0c0, align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]
[TD="align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]41[/TD]
[TD="bgcolor: #c0c0c0, align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]42[/TD]
[TD="align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]43[/TD]
[TD="bgcolor: #c0c0c0, align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]44[/TD]
[TD="align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]45[/TD]
[TD="bgcolor: #c0c0c0, align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]
[TD="align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]
[TD="bgcolor: #c0c0c0, align: left"]MF[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]
[TD="align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]49[/TD]
[TD="bgcolor: #c0c0c0, align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]50[/TD]
[TD="align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]
[TD="bgcolor: #c0c0c0, align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]52[/TD]
[TD="align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]53[/TD]
[TD="bgcolor: #c0c0c0, align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]54[/TD]
[TD="align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]55[/TD]
[TD="bgcolor: #c0c0c0, align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]56[/TD]
[TD="align: left"]DF[/TD]

[TD="bgcolor: #cacaca, align: center"]57[/TD]
[TD="bgcolor: #c0c0c0, align: left"]GK[/TD]

[TD="bgcolor: #cacaca, align: center"]58[/TD]
[TD="align: left"]GK[/TD]

[TD="bgcolor: #cacaca, align: center"]448[/TD]

[TD="bgcolor: #cacaca, align: center"]449[/TD]
[TD="align: left"]Follow Us[/TD]

[TD="bgcolor: #cacaca, align: center"]450[/TD]

[TD="bgcolor: #cacaca, align: center"]451[/TD]

[TD="bgcolor: #cacaca, align: center"]452[/TD]

[TD="bgcolor: #cacaca, align: center"]453[/TD]

[TD="bgcolor: #cacaca, align: center"]454[/TD]

[TD="bgcolor: #cacaca, align: center"]455[/TD]
[TD="align: left"]Latest Tweets[/TD]

[TD="bgcolor: #cacaca, align: center"]456[/TD]

[TD="bgcolor: #cacaca, align: center"]457[/TD]
[TD="align: left"]Star Man: Eden Hazard #lovemidweekpoints[/TD]

[TD="bgcolor: #cacaca, align: center"]458[/TD]

[TD="bgcolor: #cacaca, align: center"]459[/TD]
[TD="align: left"]Chelsea are slowly making up for abandoning us for Club World Cup #lovemidweekpoints[/TD]

[TD="bgcolor: #cacaca, align: center"]460[/TD]

[TD="bgcolor: #cacaca, align: center"]461[/TD]
[TD="align: left"]RT @daddy_spoon: @dreamteamfc how much is benteke? A must have - £2million. 75 points and counting #thebelgiansaretakingover[/TD]

[TD="bgcolor: #cacaca, align: center"]462[/TD]
[TD="align: left"]Top FAQ's[/TD]

[TD="bgcolor: #cacaca, align: center"]463[/TD]

[TD="bgcolor: #cacaca, align: center"]464[/TD]

[TD="bgcolor: #cacaca, align: center"]465[/TD]

[TD="bgcolor: #cacaca, align: center"]466[/TD]

[TD="bgcolor: #cacaca, align: center"]467[/TD]

[TD="bgcolor: #cacaca, align: center"]468[/TD]

[TD="bgcolor: #cacaca, align: center"]469[/TD]

[TD="bgcolor: #cacaca, align: center"]470[/TD]

[TD="bgcolor: #cacaca, align: center"]471[/TD]

[TD="bgcolor: #cacaca, align: center"]472[/TD]

[TD="bgcolor: #cacaca, align: center"]473[/TD]

[TD="bgcolor: #cacaca, align: center"]474[/TD]

[TD="bgcolor: #cacaca, align: center"]475[/TD]

[TD="bgcolor: #cacaca, align: center"]476[/TD]

[TD="bgcolor: #cacaca, align: center"]477[/TD]

[TD="bgcolor: #cacaca, align: center"]478[/TD]

[TD="bgcolor: #cacaca, align: center"]479[/TD]

[TD="bgcolor: #cacaca, align: center"]480[/TD]

[TD="bgcolor: #cacaca, align: center"]481[/TD]

[TD="bgcolor: #cacaca, align: center"]482[/TD]

[TD="bgcolor: #cacaca, align: center"]483[/TD]

[TD="bgcolor: #cacaca, align: center"]484[/TD]

[TD="bgcolor: #cacaca, align: center"]485[/TD]

[TD="bgcolor: #cacaca, align: center"]486[/TD]
[TD="align: left"]WE ACCEPT THE FOLLOWING CARDS[/TD]

[TD="bgcolor: #cacaca, align: center"]487[/TD]

[TD="bgcolor: #cacaca, align: center"]488[/TD]

[TD="bgcolor: #cacaca, align: center"]489[/TD]

[TD="bgcolor: #cacaca, align: center"]490[/TD]

[TD="bgcolor: #cacaca, align: center"]491[/TD]

[TD="bgcolor: #cacaca, align: center"]492[/TD]

[TD="bgcolor: #cacaca, align: center"]493[/TD]

[TD="bgcolor: #cacaca, align: center"]494[/TD]
[TD="align: left"]'The Sun', 'Sun' and 'Dream Team' are registered trademarks. All rights reserved. © News Group Newspapers Ltd, 2012/13.[/TD]

[TD="bgcolor: #cacaca, align: center"]495[/TD]
[TD="align: left"]News Promotions Limited is licensed and regulated by the Gambling Commission (Great Britain) License No. 000-001742-R-104050-001[/TD]

[TD="bgcolor: #cacaca, align: center"]496[/TD]

[TD="bgcolor: #cacaca, align: center"]497[/TD]
[TD="align: left"]Accept Cookies[/TD]

</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[patel45]

Show us the final result

 

[Novelec]

Hi David,

Is your data in this column always continuous? That is, in the column listing the GK, DF, MF, ST values, are there ever blank cells part way through the values you want to keep? Or, are there values that are different to GK, DF, MF, ST...?

 

[MickG]

Perhaps this:-

[COLOR="Navy"]Sub[/COLOR] MG30Dec57
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] IsError(Application.Match(Dn, Array("GK", "DF", "MF", "ST"), 0)) [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Rick Rothstein]

Does this macro do what you want...

Sub ReduceData()
  Dim LastRow As Long, StartRow As Long, EndRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  StartRow = Evaluate("MIN(IF(A1:A" & LastRow & "={""GK"",""DF"",""MF"",""ST""},ROW(A1:A" & LastRow & ")))")
  EndRow = Evaluate("MAX(IF(A1:A" & LastRow & "={""GK"",""DF"",""MF"",""ST""},ROW(A1:A" & LastRow & ")))")
  Rows(EndRow + 1 & ":" & Rows.Count).Delete
  If StartRow > 1 Then Rows("1:" & StartRow - 1).Delete
End Sub

 

[Novelec]

David,

Both Rick and Mick's solutions are far superior to mine, however I spent a bit of time writing mine (VBA amateur) so I will post anyway...



If you decide to use my snail-like code, simply change the range (highlighted in red) to suit your data.

Good luck with your football!

Sub Delete_Rows()


Dim Cell As Range
Dim Rows_To_Delete As Range
Dim Number_of_Rows As Integer


Set Range_To_Check = Range("[COLOR=#ff0000]A1:A200[/COLOR]")


    For Each Cell In Range_To_Check
        
        If Cell.Text <> "ST" And Cell.Text <> "MF" And Cell.Text <> "DF" And Cell.Text <> "GK" Then
                        
            If Rows_To_Delete Is Nothing Then
                Set Rows_To_Delete = Cell
            Else
                Set Rows_To_Delete = Union(Rows_To_Delete, Cell)
            End If
            
        End If
    
    Next
    
        Rows_To_Delete.Select
        Selection.EntireRow.Delete


End Sub

 

[David Wale]

Firstly I would like to wish you all a Happy New Year.

Secondly, I would like to thank MickG, Rick Rothstein and Novelec for the exact solution I required, and the interest shown by

Patel45.

Since joining Mr. Excel forum in the summer, I have asked for a number of solutions to problems, which have always been

answered.


So, thanks to all of you who have made my spreadsheet activities more rewarding by the week.


Yours cordially


David Wale