Posts by msb221

    Re: Highlight used cells for pivot report selection

    Thanks for trying, i just cant seem to get it to work. When it does work, ive had to change all the 'text to numbers' with that little diamond square that pops up.

    The main problem is how the data is being copied i think. All the data is stored on a sheet named 'Data'. It is copied over using this formula..

    Set pasterange = sht2.Range("C65536").End(xlUp).Offset(1, 0)
    Selection.Copy Destination:=pasterange

    The original data has no problems in how the numbers are stored, so no green triangles.

    But after its been pasted into data2 for my operation the whole list has green triangles and thats why the pivot table isnt working, it displays nil values.

    Is there a special paste thing i need to add onto that code because i cant figure this out.

    Re: Highlight used cells for pivot report selection

    Actually the code works fine, its just that my data has the usual 'Number stored as text problem'. The only trouble is that it doesnt go away unless i select the cells manually and click the little worning diamond and then 'convert text to number'.

    I have tried macros that select the whole column and multiply the number by 1 to convert them but this only leaves a trail of zeros to cell 65555 or whatever.


    Re: Highlight used cells for pivot report selection

    Thanks for the input. Unfortionatle it only worked once, but even then it was incorrect.

    What happened was that all the products appeared but the numbers were zero all the way down, so it wasnt recognising them.

    Also the part which seems to be causing trouble is the 'Range'

    In break mode after i rolled my mouse over the range it appeared as 'Data2!C2:C3' thats it! When it should be something like B1 - CX

    This is a real pain in the arse :)


    This is an easy one for most of you.

    Below is my code to create a pivot table. The data is located on a 2nd sheet named 'Data2' in columns B & C.

    As you can see the range is already defined. How can i have this as a variable which holds the current address of the automatically selected - occupied cells in columns b-c???

    So in order a macro that can..

    1) select all occupied cells in colums b-c
    2)paste this range as an address in d1
    3) assign a variable which has the range for the pivot table macro to complete its work.

    Thanks in advance :ufo:

    Re: VBA to AutoFilter list without zeros

    Thanks for reading. Yeah thats right it only works on column P.

    I dont need it to work on all columns because they contain other data that is irrelevant. If you look at the code notice that after the autofilter has been applied the computer selects the visable records, copies them and pastes into another sheet.

    All i need is for the autofilter to work correctly so that everything else can do its job. I find it bizzasr that its not working correctly because the autofilter works fine manually.


    Hi Guys,
    I have a perculiar problem. Imagaine 'Sheet 1' is the master, all data is stored here. There is a combination of numbers and text in most fields and the cell formatting is set to 'Text'.

    Now, all my existing macros work pefectly.

    I am trying to filter all the records in the specified colum to hide rows that contain '0' zero. This is simple when i use the toolbar and apply the custom autofilter manually. However when i use VBA it does the complete opposite.

    I have tried converting all the cell formats to 'General', 'Text', 'Number' you name it. Still failing.

    I have also tried various other was such as inputting a qeustion mark instead of a zero, meaning that anything less than a charecter should be hidden.


    Please take a look at a sample of my code below, and see if you can add any suggestions for correcting the Auto Filter feature.

    Thankyou in advance ;)


    Hi Guys,
    I want to apply an autofilter using VBA which sorts my data between dates.

    The dates will vary so they have been declared as variables, which the user will be prompted to input via a message box.

    The trouble is that the variables can be input into the autofilter code but it is then read as a plain date, it doesnt know that i want data greater than one date and less another.

    When i add these symbols before my variables > < as seen below, it cannot interpret them. If i do this >"startdate" then it mistakes my variable as a text criteria.

    How do i go about solving this problem, my brain is at wits end?

    Mark :rambo:

    Re: Finding records between dates

    Although i can see how it works, its doent work on my machine because it says that some form elements and stuff are missing.

    Anyhow, ive adapted the code to have pop-up message box's instead.

    The trouble is how do i write a variable into the autofilter code as a criteria?

    It would work if there was no less than or greater than signs infromt, and there was 1 criteria. But there is two and i need to specify one greater and one less than.

    Does anybody understand?

    Here is my code so far.

    please help asap :)



    Re: Finding records between dates

    Quote from grove

    See if this gives you any ideas?


    There used to be a zipped file here with the date filter. I downloaded it but there was no macro contained in the document?

    Can you post it again with the macro in please :p


    Re: Count Total of no of records in a sheet

    WOW! Thanks, such a simple but elegant solution.
    Ok ive just hit my second challange, but once this is done im smokin!

    When the auto filter is applied it still counts every record, how do i get it to count only the visable records?

    Im sure there is an ease to this too, but i cant figure it out.

    Mark x


    Hello people,

    I desperately need some help regarding Excel 2002-2003 issues.

    I created a macro driven user friendly spreadsheet in Excel 2002.
    The problem is when i use it in 2003 i get the following error message constantly when i click a macro.

    'Cant find project or library'

    Here is a small example of the code for which this error occurs.

    It highlights 'Lastrow' but i see nothing wrong with that command or my code. The same thing happens to various parts of perfectly functioning code all through the spreadsheet.

    Heres the bizzar part. It opens and runs fine on half the company network computers with Excel 2003, while the other half with Excel 2003 it stuffs up. All the computers have exactly the same verison. Why should it stuff up for some and not the others?

    Its very bizzar.

    Please help as this is crucial to what im doing and will mean that months of work has gone down the pan. Then i will have to recommend macintosh to the IT department kos there is no reason to stay on pc. so prove me wrong. :rock:

    Mark. :thanx:

    Hiya :rock:

    I have data in cells A1 - D4.

    I want Excel to Open Outlook and copy the contents of this selection into a new mail message ready for sending at the click of a macro!

    Is this possible?!!!

    So far it has been a hard task finding anything simple and coherent to understand.

    Thanks, :thanx:

    Hi Guys,
    How do you search for records between dates? For example.

    I have my dates stored in this format 10.10.05 - I want to be able to input dates in the same format and search between say 10.10.05 - 10.11.05.

    How do you do this with a macro that when clicked displays a message box to input the 1st and 2nd date, then displays only records between those dates?

    HOWEVER: There is a small problem. When you click on the cell displaying a dat like this - 10.10.05, it shows in the bar above as 10/10/05. Is this confusing the search?

    Thankx in advance.

    Mark B. :gift:

    Hello Fellow Excellerz,

    In my spreadsheet i have it designed like a form where by users input comment in the text box. However, when what you type is longer than the text box width it doesnt move onto the second line automatically.

    How can i fix this problem?


    Re: Find + Update

    Hi roy, thats not what im looking for.

    Im looking to seach my main sheet of data 'Data' by eg. ordernumber '123'

    The whole row of data for order'123' is then copied & pasted into 'data2' from cells G2-Z2.

    Those cells are linked to text boxes on another form, so the fields are shown in the text boxes. The user should modify the record from the text boxes, as the cells will change at the same time.

    Then to finalise, the user hits another macro button to paste the newly updated row over the existing one in the main sheet 'Data'

    Hopefully this makes sense.

    Thanks :)

    Hello there.
    Please does anyone have an idea on how to Search for a record by number, eg 442. And replace it with another more upto date record (from another sheet) by copying the new data over the existing row, rather than adding it onto the bottom.

    If anyone knows a macro to do this, i would be very grateful

    Many thankx in advance!

    Mark :D

    Re: AUTOHIGHLIGHT cells and give reference

    Oh yeah i forgot to mention that, well i overcome the problem by changing

    MsgBox rngeAllCells.Address

    by writing

    MsgBox rngeconstants.Address

    That gave me the selected cells range.
    but now im gonna try the pasting technique! :thanx:

    Thanks!! :)