Posts by bassmaninaus

Yes I have power pivot.

Attached, thanks in advance.Book1.zip

Thanks KjBox. I was playing around with it and changed the code below and its no longer crashing. No idea why you'd need to say what sheet the range is on as you have to be on the same sheet when runng a worksheet change macro..

Thanks though, if I have any other issues I'll try your code.

Set MyRange = Range("B14:B1048576") changed to 
Set MyRange = sheet1.Range("B14:B1048576")

Hi, I have a pivot where the daily rows need to be summed, however over the month summing doesn't make sense, I'd like the average over the month displayed.

Data Table

Resulting Pivot table

The daily rows are correct, summing Groups A & B, however I'd like the Total row to be an Average of the daily figures, so in this case I'd like it to show 20.

Thanks in advance.

I have a Worksheet Change code, below being the start, however when executing the last line, excel shuts down/closes. 'If Not Intersect(Target, MyRange) is Nothing Then'

Can anyone see anything wrong with the below code?

Thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)


Dim MyRange As Range
Set MyRange = Range("B14:B1048576")


Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


If Target.Cells.Count > 1 Then GoTo exitHandler

If Not Intersect(Target, MyRange) Is Nothing Then

Re: Change/Mask Client Sensitive Data

Yep done...thanks again!

Re: Change/Mask Client Sensitive Data

Holycow, works perfectly, thanks so much!!!

Hi all,
I have a long list of data (around 10,000 rows) with one column Client Sensitive (Column A see below) that needs to be changed before using in a pivot so the client won't see this data.
At the moment I'm using a formula but happy to use VBA for this as the rest of the report updates with VBA.

The formula that works but takes far too long is
=IF(COUNTIF($A$1:$A2,$A2)>1,VLOOKUP($A2,$A:$R,18,FALSE),MAX($R$1:$R1)+1)
Where column A is the sensitve data (and the unique identifier) and column R is the column the formula is entered.

The below is what I want, the column R I now use in a pivot to get the results I want then I can hide that column and delete Column A so no Client Sensitve data is with the spreadsheet.
However as I've said it takes far too long to calculate down 10,000 rows..Any ideas to make this quicker??????

Thanks
[TABLE="width: 96"]

[TD="bgcolor: transparent"]
[TABLE="width: 152"]

Column A

Column R

A12345

[TD="align: right"]1
[/TD]

A12346

[TD="align: right"]2
[/TD]

A12346

[TD="align: right"]2
[/TD]

B12347

[TD="align: right"]3
[/TD]

B12357

[TD="align: right"]4
[/TD]

C12547

[TD="align: right"]5
[/TD]

C12455

[TD="align: right"]6
[/TD]

C12455

[TD="align: right"]6
[/TD]

[TD="align: right"][/TD]

[/TABLE]
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]

[/TABLE]

Re: Delete blanks using Special Cells

Thanks jindon

Re: Delete blanks using Special Cells

OMG that works, would you know why this works but what I was using wouldn't? The data is client addresses, I obviously changed the addresses to numbers for privacy. It has come out of the clients system.
Thanks so much

Re: Delete blanks using Special Cells

Sorry yes, wrong file. However three other people in my office can't get the second file to run..oh well back to the drawing board.

Re: Delete blanks using Special Cells

Thanks Jindon, still not working for me, can we try this another way, can you open up the attached and click the button, does it run?

Re: Delete blanks using Special Cells

It's not highlighting and deleting(moving to left) cell C3. It keeps saying 'no cells found'

The code isn't the issue, it's the attached spreadsheet, try the code on it.

I am trying to delete all blanks in a range by using the following code

Range("A2:F7").Select 
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlToLeft

The above code does not work on cell C3, can anyone tell me why. The cell is blank but don't know why special cells(blanks) won't work on it.
The attached range comes from a client, this is just a small range out of about 25,000 rows with a large number of these cells where the code won't work.

Thanks