Posts by Kieran

    Re: Merging workbooks - error message "too many cell formats"


    The following code I obtained from the web - Sorry I can't remeber the original author - will remove extra cells/rows/columns and formats.

    Just be sure to save your work before you do it.

    Over time, you may also accumulate a lot of custom number formats. I use the following code to get rid of them, based on an example by John Walkenbach:

    Re: insert a row using VBA


    You are right, the pivot table needs to be refreshed.

    This is done by right clicking in a cell inside the pivot table and sleecting refresh data, or by selecting the refresh data control on the pivot table toolbar, or using the refresh data control ont he data menu.

    Alternatively, you can have it done automatically with something like this

    the code will need to be placed in the worksheet that contains the pivot table.


    I learnt 'this stuff' through making mistakes, just like anyone else.
    Along the way, I found out about the various boards and found them invaluable, not just for posting questions, but there are a large number of very clever people contributing to them.
    Their solutions also help a lot.
    Where I can, I try to repay in kind.

    As far as books go, I could not offer any real suggestions as I have not bought any, however if you look around the forum, there are plenty of good references. (Dont forget Dave Hawley's books too - it must be good, he owns this place and doesn;t live that far from me too!)

    As far as releasing memory in VBA, it is nt really necesary unless you are dealing with objects, then you should 'Set ObjectName= Nothing" at the end of their usefulness to realease memory. VBA 'normally' cleans the rest up for you as you exit the routine. As you seem to realise however, it is good programming practice to explicitly release the memory anyway.
    There may be a few other conditions on this, maybe other readers will be able to help.

    Try this (untested)


    Charts exist in the Charts collection if not embedded, as against the worksheets collection if embedded.

    The code will probably need to be amended to go through the charts collection in a similar manner to the worksheets collection.

    The results of the debug messages are shown in the immediate window. ( Go to the VBA editor menu option View, option Immediate window.

    The results of the test will be visible there. Please let the forum knwo the results.


    Without looking at the rest of the code, it seems to me that Left never = True for this to happen.
    Try adding two debug statements around the
    If Left = True Then line


    debug.print left, "left option test next "
    If Left = True Then
    debug.print left, "left option selected" and then rerun the procedure and see what the values are.




    This is a quick tought, so I may be wrong, but the data looks reasonably formatted.
    Why don't you sue the text to columns tool to separate the question number from the answer.

    you can then use a pivot table to summarise the responses.


    You can access the closed workbook and make a local copy of the data for lookup purposes.

    an array formula like the following will retreive the values
    ='file path\file name.xls'!range name

    This approach will only work for fixed length arrays.


    You could either use the date functionsin excel to determine the firtst day of the current mont and and the month after and then subtract the differnce, or create an array that holds the corresponding number of days for each month. As you already have a combo box, I imagine that there must be an array or range that holds these values and you could do this easily. (not allowing for leap years that is, in that event I suggest that you use the 'calendar method')