PDA

View Full Version : Amend Code -



keilah
08-07-2007, 03:43 AM
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

xld
08-07-2007, 04:13 AM
What do you mean? Row 1 is a temporary row inserted for the autofilter, so it needs deleting.

rory
08-07-2007, 04:59 AM
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
08-07-2007, 05:01 AM
PS A bit late to ask, but is it OK to post a link to other sites like that?

mdmackillop
08-07-2007, 05:46 AM
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
08-07-2007, 06:01 AM
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!

xld
08-07-2007, 06:05 AM
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
08-07-2007, 06:12 AM
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...

xld
08-07-2007, 06:13 AM
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
08-07-2007, 06:27 AM
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!

xld
08-07-2007, 06:31 AM
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
08-07-2007, 06:37 AM
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.

xld
08-07-2007, 06:53 AM
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
08-07-2007, 07:09 AM
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.

xld
08-07-2007, 11:57 AM
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
08-07-2007, 02:35 PM
That's nowhere near my most annoying habit!
But thanks for the feedback...

xld
08-07-2007, 02:55 PM
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!