Posts by Neale

    Not a solution but an exceuse maybe

    Excel 2000 has a limit of 64MB of RAM - no matter how much the machine has.

    Excel XP has a limit of 128MB and Excel 2003 has better memory management and uses most of the memory available.

    Pivot tables are memory hungry. From my experince a 44MB excel file in Excel 200 is usually pretty unstable anyway as it is approaching Excel 64MB limit just by opening it.

    Are there any calculations you can take out - eg paste special values to remove calculations.

    Are there some columns you can delete as they are not used or important??


    CONGRATULATIONS to Dave and the others involved with the forum on it being up and running for a year.


    Its a great community and it is great because of its members and their willingness to go out their way to help others.

    May it last for many more years.


    It can depend on where the numbers came from, ie where they downloaded / imported from another system?

    If you format them to two decimal places and them use the sum you should see zero.

    I've had the same problem - mainly on imported data, XL obviously thinks there more decimal places than are displayed.


    I use this macro attached to a button. You could use it in a Before Print macro.

    I use a range named HideRange for the values to be checked.


    Sub HideZeroLines()

    Dim c
    Application.ScreenUpdating = False

    With Worksheets("report").Range("hiderange")
    .EntireRow.Hidden = False
    End With

    For Each c In Worksheets("report").Range("hiderange")
    If c.Value = 0 Then

    c.EntireRow.Hidden = True
    End If
    Next c

    ActiveSheet.DisplayAutomaticPageBreaks = False
    Application.ScreenUpdating = True

    End Sub

    Wecome to the forum

    This may be too complicated for waht you want but here goes.

    Assuming you want to look up a number of occurances. This array formula find the nth occurance. So you could input 10 of them to find the first 10 occurances.

    In this example the table is 5 columns wide and its ref is A1:E303. The 5 at the end of the formula means you want the data in the 5th column. Cell Ref G4 contains the number of the occurance to the look for as a number. Cell ref H4 has what to look up.


    This is an array formula so you need to Hold down three keys Ctrl + Shift and Enter after entering it in a cell. This will put {} brackets arounf the formula to indicate it is an array.

    As I said it may be too complicated but it will allow you find more than the first occurance.


    Welcome to the forum

    Can't you import all your data first andf them once its done format the column as Wrap text and then use AUtofit rows to make sure its all visible

    something like

    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Selection.RowHeight = 12

    This was just recorded.


    To all the ozgridders and their families around the world I hope you have a Happy and Safe Christmas.

    Thanks for all your help during the year.

    (Its going to be about 35 degrees celsius in Perth for Xmas day this year. A great day for the pool or beach.)

    Best wishes

    Yes have looked at xlhelp.

    Unfortunately xlhelp doesn't always tell you the downside of excel's features - and it rarely quantifies them.

    Hi All

    Anyone had any problems / issues when using Tools > Track Changes.

    I'm about to use it on some large workbooks and I don't want their file sizes to blow out.

    I'm using XL2000 and likely to change to XP soon.


    Hi All

    Does anyone have some links or experiences with having two versions of Excel installed - specifically XL2000 and XLXP?

    I've got 2000 installed and wish to add XP rather than upgrade - they are both full versions.

    Any suggestions (what to look out for) appreciated.


    Welcome to the forum

    Unfortunately I think there is a limitation to the OFFSET function as far as unopen workbook s are concerned.

    This formula may help depending on how big your data is.

    Cell G1 contains your first column value, cell H1 contains the value to look up.

    The table is in A1:E3. the formula look like this


    Should work on a closed book.


    Welcome to the forum.

    What you could do is to create a list of number you want ot enter into your input range.

    Then something like this will do it. (select the range of input first before running the macro). Assumes A1 is input cell and B1 is output - change to suit.


    Sub CheckOutput()

    Dim c
    Dim StartValue

    StartValue = Range("a1").Value

    For Each c In Selection

    Range("A1") = c 'put value in input cell
    c.Offset(0, 1) = Range("b1").Value 'extract value form output

    Next c

    Range("a1") = StartValue

    End Sub