Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
VBA and me dont get on. Never needed to do more than smash a macro up a bit.
A colleague has a VBA query which I cant fix, and well, frankly she's pretty so come on guys time to help ol' Stoner out for a change 🙂
can some one compile me some code that sets the filter field of the last column in a pivot table to <>0. The pivot table, when generated, could have any number of columns in it, but the last one (whatever number field it is) should always be set to <>0
thinking Dim'ing "last column" as columns counted in the pivot table and then set field=last column, but havent a clue on the syntax.
cheers guys.
This might help. It returns you the last cell for a sheet. You can then set it by saying your variable.value= "<>0"
Public Function LastCol(Optional ByVal sSheet As String = "") As String
'FIND THE LAST COLUMN FOR first ROW IN THE SUPPLIED SHEET
ThisWorkbook.Worksheets(sSheet).Activate
LastRow = Range("$IV$2").End(xlToLeft).Row
ThisWorkbook.Worksheets("Sheet1").Activate
End Function
cheers samuri, but you're going to have to wind the dial further back towards "stupid" for me.
Can you take me through dim the field number from that bit above and then using that dim in the pivot table filter command...
I really am crap at VBA 🙁
Right You need to get my name right first!
sub bob()
dim fred as string
fred =LastCol("your sheet name here")
Range(fred&"your row number here").Value = "<>0"
end sub
Public Function LastCol(Optional ByVal sSheet As String = "") As String
'FIND THE LAST COLUMN FOR first ROW IN THE SUPPLIED SHEET
ThisWorkbook.Worksheets(sSheet).Activate
LastRow = Range("$IV$2").End(xlToLeft).Column
ThisWorkbook.Worksheets("Sheet1").Activate
End Function
You will still need an event to fire this or a button or something.. Samuari indeed *walks off shaking head*
doh, sorry rich 😉
thanks for that.
although embedding a chippy, lippy cockney in my code goes against the grain a little 🙂
shall go and try it out. will let you know if I die in a ball of runtime errors and flaming ostriches.
if i wasn't having to suffer a Holiday, i could be a bit more helpful, i don't even have a copy of Excel.
I did something similar, i think it used the Pivottable.filter type of syntax.
If you do something similar to what you want too, but do it whilst recording a macro then hack the code to get it to do what you want in the real case.
in the end we just used a filter outside of the pivot table.
Ill come back to it some time and tidy it up. I really should get to grips with VBA but it goes against my declaration not to learn anything new since 2003.
Stoner; I'm interested in this (bad I know) but don't really get what you mean by setting a pivot table column (row field? grouping value?). If you want to send me an example file I'll have a crack at it for you.
jimjamesjamesh (at) googlemail.com