Amend Code -

[keilah]

Hi Quicky

Need to amend the following code not to delete the first row (i.e row a1 col A from the master sheet ("EligiblePortfolios")........any ideas....

Public Sub DeletionCriteria()
Dim mpCriteria As String
Do
mpCriteria = InputBox("Please delete OLD portfolios")
If mpCriteria <> "" Then
Call DeleteData(Worksheets("EligiblePortfolios").Columns("B:B"), mpCriteria)
Call DeleteData(Worksheets("ActualData").Columns("E:E"), mpCriteria)
End If
Loop While mpCriteria <> ""
End Sub

Private Sub DeleteData(pzData As Range, pzCriteria)

pzData.Parent.Rows(1).Insert
pzData.Cells(1, 1).Value = "Temp"
pzData.AutoFilter field:=1, Criteria1:=pzCriteria
pzData.SpecialCells(xlCellTypeVisible).EntireRow.Delete

End Sub

[Bob Phillips]

What do you mean? Row 1 is a temporary row inserted for the autofilter, so it needs deleting.

[rory]

This was asked and answered here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22746157.html

Regards,
Rory

[rory]

PS A bit late to ask, but is it OK to post a link to other sites like that?

[mdmackillop]

Hi Rory,
Certainly is. You can also hide the link behind your text by selecting it, clicking the Inset Link button, and pasting the address like this (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22746157.html).

[rory]

Thank you kindly!
I also wasn't sure if it was worthwhile, as I can't recall whether EE scrambles the posts if you aren't a member!

[Bob Phillips]

Thank you kindly!
I also wasn't sure if it was worthwhile, as I can't recall whether EE scrambles the posts if you aren't a member!

No, it just scrambles your brain trying tow ork out what is the point of the meaningless points scoring, and keeping another responder off your back when he thinks he deserves points given to you (arguing about virtual merits, god help us).

[rory]

I take it you're not a fan? :devil2:
I've never worried about the points - I'm only in it for the glory, the women and the T-shirts...

[Bob Phillips]

Well I never even got any of them, not even a t-shirt. Just loads of points that expired.

What does temporarily mean in terms of Excel MVP?

[rory]

I've got 2 T-shirts, both of which are the subject of much ridicule from my wife (though now I think about it, it may be me that's the subject of said ridicule...)
Well it's an annual thing, as I understand it, and I'm not sure how I got it in the first place; so I'm trying not to get too attached to it!

[Bob Phillips]

So are you profiled, and where are you located? I heard recently there were quite a few in the UK, and I couldn't place them all, you were clearly one I was not aware of.

[rory]

Yeah, I'm on the list - about number 90 odd as I recall.
I live in East Sussex but work in London. Impressed by your decision to quit your job and go freelance - it's one of those things I often think about but never quite have the nerve to do.

[Bob Phillips]

I see it. Just spotted another name that I hadn't realised had been awarded.

As to work, after a while you just can't take the corporate politicking and bitching. In my last 3 years, there were 5 re-organisations, which meant you had to spent acres of time networking, doing meaningless things, just to try and position yourself for a decent job. What with the insanely complex (and pre-detrmined, and therefore pointless) annual reviews, it was difficult to actually do the job that I loved.

[rory]

I left a job like that about a year and a half ago. Not sure I could put up with it again.
I've never had a meaningful annual review at any company where I've worked, and don't expect to anytime soon.

[Bob Phillips]

I see you have already adopted that most annoying of MVP habits -

Post #n: OP says: Thank you for your help

Post #n+1: MVP says: You're welcome. Thanks for the feedback

[rory]

That's nowhere near my most annoying habit!
But thanks for the feedback...

[Bob Phillips]

I only said that most annoying of MVP habits, that doesn't preclude you from having worse.

And I waive my right to thanks for this feedback!