Code clean up help

  • Good morning,
    I am new to the forum, I joined seeking some experienced assistance. I am hoping that someone would be willing to help me clean up the code below. Obviously I do not have experience with writing macros but through trial and error the code is working basically. I really need to to perform faster, it is a slow to execute code and I was hoping that a more experienced person could identify and clean up some of the issues that I suspect are in the way I wrote this macro.

    Thank you for looking.

  • Hi

    If you remove the clear contents lines and replace them with this – you save 27 lines of code.

    Range("B2:G10000, K2:l10000, X2:AD10000, AT2:AQ10000, AV2:AV10000, AZ2:AZ10000, BB2:BB10000").ClearContents

    If you post your workbook I will make your code clean. A significant change would be to have 3 cells called

    bid breakdown type 1
    bid breakdown type 2
    bid breakdown type 3

    In these cells you would type your bid criteria – I never use input boxes in code as it stops the code where the method above will not.

    Hopefully we will see your file soon if not email it to me directly.

    Take care


  • Surprise, surprise.
    Mr, I assume it is Mr, Smallman did not question why it has, on multiple occasions


    I mean the 10000 and the .Select is a given.

    Just joking here but admiring your willingness to wade through all of the code.

    Merry Christmas and a Happy and Prosperous New Year.

  • LOL - yes you know how I think...... I was going to say something about the selection but it was mostly recorded code and I figured the final version would have spelt that out. I wanted to get rid of the sheet references mainly and needed the file to nail that down. Up to the Op from here. At this time of year I am time rich and love to roll back the clock and do this sort of thing once more. Take it easy jolivanes!!!!!

    Mr Smallman :)

  • Jolivanes, Smallman,
    I am learning this all through trial and error and I built this workbook from scratch in order to organize our bids. Clearly I know very little about proper coding, hence the reason for the post. I have the workbook functioning as I would like it to function but now I am trying to get it cleaned up. This thing quickly got out of my control.
    I have a link posted to the workbook file location below (BID WORKBOOK v.6.30beta) and in the link are a few samples of how the workbook operates. The workbook is password protected (just to keep folks from corrupting date while using it.) You can unlock it by hitting CTRL/END on the recap page, that will take yo to the unlock button.

    Please let me know if you can help clean this up to function quicker and if you are willing to clean up the entire workbook please PM me with a fee with which you would ask to do so as I now realize that this may be a bigger job than I thought.
    Thank you for your assistance.

  • I would love to eliminate input boxes. The workbook needs to have user selectable options in the three pop ups of: "Bid Item, Drawing, Area, Phase, System, of Viewpoint Codes" the only other way I could think of to do that would be a user check box located on the RECAP page where they check the selection of how the bid should be broken down. I just didnt know how to set that up.

    The individual code ranges were entered that way because there is content in other cells that I did not want to remove.

  • There was no negative intentions in my thread. Just a little dig at Smallman. It is holiday season after all. I don't subscribe to being politically correct so we'll just go from there.
    It is commendable what you are doing so just keep it up.
    A small example on where to clean the code.
    Avoid using select.


    Sheets("ACCUBID EXTENSION").Select
    Sheets("BID BREAKDOWN").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    could be changed to something like this

    With Sheets("ACCUBID EXTENSION").Range("AL3:AL799")    '<----- Why 799?
        .Copy Sheets("BID BREAKDOWN").Range("C4")
    End With

    But Smallman will get you on the right track.

  • Jolivanes
    I didnt take it as a negative comment, no worries.
    I expected a little bashing after all since I am sure what I did is super sloppy at best.

    You say don't use select??? I wont ask why not but I will say I did that an awful lot. oh boy. At least this will be the holiday gift that keeps on giving. lol
    That's a nice option, thanks

    799 is because where it is pasted has a line limit.

  • While Smallman is sweating away, one other little comment.
    At the beginning of the code, Application.ScreenUpdating, as well as the others, should be set to False and at the end to True (as you have it here) again.

  • While Smallman is sweating away, one other little comment.
    At the beginning of the code, Application.ScreenUpdating, as well as the others, should be set to False and at the end to True (as you have it here) again.

    So I knew about that one but I have something dirty in the other codes that tends to hang up occasionally and the patch was to add that as a "lets just start fresh shall we' bit.
    Thanks for the comments, I glad to have the input.

  • LOL - "while Smallman is sweating away". Yesterday I posted a few times on Ozgrid and then I went to the pub - the perfect day!!!! I am planning on repeating the dose today so this is my first look at your problem in 24 hours. And I would suggest I will be heading out to knock the top off a cold Christmas beer very shortly. It is early here in Oz but somewhere in the world it is 5pm. Bottoms up guys!!!!!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!