How come the Macro is not working?

[feipezi]

Hi,

I have a Excel Macro to unselect (or uncheck) some existing fields of a Pivottable but it's not working.

Any idea why?

Thanks in advance.


Sub RemoveDataFields()
For Each pf In ActiveSheet.PivotTables(1).PivotFields
If InStr(pf.Name, "trx") Then
pf.Orientation = xlHidden
End If
Next
End Sub


But it will work if I do this (I don't like it since it is lengthy):


Sub RemoveDataFieldsx()
ActiveSheet.PivotTables(1).PivotFields("abctrx1").Orientation = xlHidden
ActiveSheet.PivotTables(1).PivotFields("abctrx2").Orientation = xlHidden
...
...
ActiveSheet.PivotTables(1).PivotFields("abctrx19").Orientation = xlHidden
ActiveSheet.PivotTables(1).PivotFields("abctrx20").Orientation = xlHidden
End Sub

 

[Loomah]

hi

try
If InStr(pf.Name, "trx")<>0 Then
or
if pf.name like "*trx*" then

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[feipezi]

Thanks Loomah. But neither way is working. This is really weird.

Take care.

 

[Loomah]

hi
can you test this by changing
pf.Orientation = xlHidden
to
debug.print pf.name

i've no real life experience of programming pivot tables (and only pass thru this forum rarely these days too) so just trying to nudge in the right direction!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[UnsolvedCoding]

Try this

Sub RemoveDataFields()

For Each pf In ActiveSheet.PivotTables(1).PivotFields
If pf.Orientation <> xlHidden then
pf.orientation = xlHidden
End If
Next

End Sub

Also if you don't use a watch window it would be helpful to do that to see what is going on. If you haven't used a watch window before its the icon with the glasses in the VBA editing enviroment.

 

[feipezi]

UnsolvedCoding,

If I used yours, all the PF will be hidden, even the PageFields; if used the following, then it wouldn't work.

Thanks.

Sub RemoveDataFields()

For Each pf In ActiveSheet.PivotTables(1).PivotFields
If pf.Orientation <> xlHidden And InStr(pf.Name, "trx") Then
pf.Orientation = xlHidden
End If
Next

End Sub

Loomah,

Here is what I tried:


Sub RemoveDataFields()

For Each pf In ActiveSheet.PivotTables(1).PivotFields
If pf.Orientation <> xlHidden And InStr(pf.Name, "trx") Then
pf.Orientation = xlHidden
Debug.Print pf.Name
End If
Next

End Sub


Then go to the Immediate Window, nothing in there. No idea why Excel cannot detect the field.

Thanks anyway.

 

[Loomah]

hi
try
For Each pf In ActiveSheet.PivotTables(1).PivotFields
Debug.Print pf.Name
Next

incidentally if InStr(pf.Name, "trx") won't do anything as the if function requires a logical test - using instr returns an numeric data type so unless it returns 0 or -1 nothing will happen.

you really just need to see how xl is seeing your pivot field names (if at all)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

so unless it returns 0 or -1 nothing will happen
Sorry to disagree.
If it returns 0 then the logical test fails.
If it returns ANY VALUE but zero, then the logical test pass.

 

[Rofeu]

Hi,

the first parameter of the InStr function is 'Start'.

Try

Instr(1,pf.name,"trx")

Cheers,

Rofeu

 

[Loomah]

PHV you are of course correct and i'm talking out of my rear!
rofeu the first parameter is optional and if omitted defaults to 1

time for me to keep quiet for a little while!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Rofeu]

true, Loomah.

Feipezi, can you try to declare the variable as a pivotfield?

add this at the top of the code:

Dim pf As PivotField

Shouldn't really make a difference, but worth a shot.

Also, you speak of 'unchecking', this makes me wonder if you're really trying to hide PivotItems instead of PivotFields, but then your second method shouldn't have worked either, I guess. Still a good thing to confirm, because the code should work fine.

Cheers,

Rofeu

 

[feipezi]

Hi,

Thank you all for your efforts. I really appreciate it.

I declared the pivot field, which did not help.

For function Instr(), it's not like a Find() func. The way I put it should be working too: x=Instr(pf.name,"trx"). The problem is that even Excel detected the fieldname with "trx" in it, it won't have it hidden. I doubt Excel couldn't detect it, don't you?

Thanks again.

 

[feipezi]

Rofeu,

No, I was trying to make the fields with the name like "xxxtrx" disappear, not the pivot items.

As you right-click on a PT, at the bottom of the dropdown list will be "Show Field List". Pick it and the field list will usually show on the right-hand side of the window, where you can unselect or uncheck the fields, not items, already checked or selected and they will vanish if you do so.

Hope I made myself clear.

Thanks.