Posts by Brewface

    Hi All,

    I am hoping someone will be able to come up with a handy way to insert the cell property "font" into an array so i can check whether it is a strikethrough or not without having to continuously call the sheet.
    Basically I am looking for something similar to this:

    FontTypeArray() = Sheet1.Range(Sheet1.Cells(17, CableLengthCol), Sheet1.Cells(LastRow, CableLengthCol)).Font

    I've tried everything from setting the array to an object, font, ect. I know you can set a variable to the font type by going

    Set z=Sheet1.Cells(17, CableLengthCol).Font

    But this does not work for arrays. If i have to loop through the array anyways I will just leave my code as is and continually check the sheet for font type. I was just trying to minimize the number of sheet calls to speed the code up significantly, as I am in the realm of 10000 lines of data.


    Re: SumIf cell is equal to any in a list

    I had thought of using sumproduct but my problem was that you could again only test one condition at a time not an entire list of conditions against one cell, unless I am missing something. It looks like I will just have make a calculation column and then drag and drop for each condition and make a matrix then sum that, simple enough. Was hoping for a one stop formula to test all conditions, but i can make this work with no issues I think. Thanks!

    Hello all,

    I have had no luck with internet searches nor racking my brain for a solution. The basic problem is this, I have a large range of data 2000+ cells and I need to Add only the hours for a division within a department.
    The division is given with a list of names. To be more clear here is an example

    Name Hours
    Doe 2
    Smith 3
    Murphy 3
    Roberts 8
    Mollard 4

    Names belonging to Division A_


    Division A Total hours:


    Usually i would just do a series of If statements but there is between 30 and 55 names in one division and this would be painstakingly tedious (I have to do this same thing for 13 divisions...)
    Ideally I would just like to Specify my Sum Range, Specify the Criteria Range, Specify the Criteria List. Exactly like a Sumif...but the Criteria can be anyone in a list. I thought of some combination of Vlookup or match might work but have had no luck so far.

    Thanks for the help,


    Hi all,

    I am hoping someone has some wonderful ideas on how to make an automatic Process Flow (flow chart), similar to what is available in the Smart Art section.
    The situation is this as I select a station it would get added to the flow, if i delete the station it is removed from the process flow.
    It's much nicer to do this automatically and represent these things visually.
    Some key information,
    The stations would be selected on a different sheet than where the process flow is.
    I think this goes without saying but the text would in each box would be the same as the station.

    That's it! I am not even sure where to start with this, so any help is appreciated. I am familiar with VBA, having dabbled in it for the last couple of years, so am not opposed to having a macro solution.

    Re: Special Validation List

    I'm in the same boat as Roy at the moment. I had assumed active meant it would appear in the list and if not active it would not appear in the list!

    Re: Special Validation List

    I'm not sure whether this is possible just through a validation list...
    Are you able to have an unused row and then just hide it? If so, you might be able to accomplish through idea would be to check each cell in the column/row for bold...something like

    Sheet("Sheet name here").Columns(List Column #).ClearContents
    LastRow=Sheet("Sheet name here").Cells(Rows.Count, Appropriate columns here).End(xlUp).Row
    For iRow=1 To LastRow
    If Sheet.Cell(iRow, Column).Font.Bold=True Then
    Sheet.Cell(iRow, Column).Copy Sheet.Cell(1, Other Column)
    End If

    And then make your validation list that Column. I'm not sure about how to trigger it though and whether a change event would capture changing it to bold or not...
    Let me know if that code makes any sense to you and or if it put you down the right path, it is very early over here in Deutschland!

    Re: Need to keep selected data visible when unhiding rows.

    I managed to solve the problem with an array, it's pretty brute force but works.


    Please don't nurture a sense of entitlement about the help you should get here. People give of their time freely here. And by the way, criticising code provided by other members will not endear you to anyone!

    I have never nurtured a sense of entitlement, and don't recall criticising anyone's code other than my own. The most I would of said would be there is probably a more efficient way of performing the required action, but that is more than likely pointed to my own code.

    Hello all,

    I am hoping someone may be able to provide the answer to this question.

    I have a list of countries which are all hidden until one is selected in a combobox. When the Country is selected that row is unhidden, this is easily done. My problem is that I want to keep that row unhidden when a new country is selected...if the positioning of the rows remained the same this would be fine; however, they are subject to change. So I am having to unhide the rows find the value and then rehide them again.
    If that explanation is unclear I will try again...

    • If a country was selected I want to keep that row unhidden
    • The row where the country appears may change depending on if a new country was added. (They are sorted by continent and must remain this way)
    • Is there an..."If visible" function in VBA?

    Hopefully i have better luck this time than previous posts...



    Re: Generate Master Sheet

    Some things to note in the code....the first part is just placing cell names into their appropriate place...the second part is placing a Sum into the individual says...just like you would do normally.
    One other thing if you are going to change the sheet names then you MUST change them here as well...this is not an elegant way to write it but it is effective.

    Re: Generate Master Sheet

    Did not test this before posting but should give you an idea what to do.

    Re: Automatic population of table from data

    Re: Automatic population of table from data

    Tested it and everything seems to work as designed. Let me know if any issues arise

    Re: Hide row if 3 cell in a column is blank in Pivot

    Quote from blueorangeko;674660

    the error is Run time error 9 : Subscript out of range

    Please read my other post carefully you MUST put quotation marks around the sheetname if you are going to designate it this way.