Posts by bassmaninaus

    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.


    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

    DateGroupNumber
    Fri 1 Jul 22Group A10
    Fri 1 Jul 22Group B15
    Sat 2 Jul 22Group A5
    Sat 2 Jul 22Group B10


    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.

    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"]

    [tr]


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

    [tr]


    [td]

    Column A

    [/td]


    [td]

    Column R

    [/td]


    [/tr]


    [tr]


    [td]

    A12345

    [/td]


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

    [/tr]


    [tr]


    [td]

    A12346

    [/td]


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

    [/tr]


    [tr]


    [td]

    A12346

    [/td]


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

    [/tr]


    [tr]


    [td]

    B12347

    [/td]


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

    [/tr]


    [tr]


    [td]

    B12357

    [/td]


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

    [/tr]


    [tr]


    [td]

    C12547

    [/td]


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

    [/tr]


    [tr]


    [td]

    C12455

    [/td]


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

    [/tr]


    [tr]


    [td]

    C12455

    [/td]


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

    [/tr]


    [tr]


    [td]

    [/td]


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

    [/tr]


    [/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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [tr]


    [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]

    [/tr]


    [/TABLE]

    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

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


    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