Posts by Rob Xaos

    Re: How to delete rows in certain column, not entire row?


    First you need to select the range you wish to delete and then use the Delete method.


    Worksheets("Sheet1").Range("C3:F4").Delete Shift:=xlShiftUp

    This would delete rows 3 & 4 in columns C - F and move the cells below up.

    The Shift parameter can have the value xlShiftUp or xlShiftToLeft. If you omit Shift parameter then Excel will make a 'guess' depending on the shape of the range you are deleting.

    I am moderately experienced with Excel and VB (2003) but I am encountering issues when handling dynamic ranges for handling lists/tables. I have always found an ad hoc solution to these issues, depending on the application. However, I am wondering if there is a consensus of what is the best practice in these cases?

    The issue arises when a dynamic range is defined for a list/table that during use may end up with zero length for one or both dimensions.

    Consider a dynamic range for a list defined as follows:


    ListOrigin is a defined name for the cell that is the origin of the list
    MaxListLength is a defined name for a cell containing the maximum length of list (to avoid doing a COUNTA on a whole column)

    There are two options I have used for ListOrigin, both of which present their own issues when subsequently handling the list in both Excel formulas and especially in VB.

    If the list is defined to include the header, then it frequently has to be trimmed off before being handled.


    Sometimes it is not necessary to trim off the header and this seems very efficient, but consider a list who's heading is 'List' but ends up by a twist of fate containing an entry called 'List', all of a sudden your VLOOKUP produces unexpected results.

    The alternative is to define ListOrigin for the first potential entry in the list. This avoids having to trim the header off for formulas and VB code. However if the list is empty then it turns the dynamic range into a #REF error. It is easy to create Excel formulas to handle but in VB it becomes quite troublesome.


    This solution can be clumsy particularly during development when the VB code may throw a 424 error for other reasons that you don't want handled as an empty List.

    Perhaps someone out there has an altogether more elegant way of dealing with this whole issue that I have not considered, or simply can give advice as to what would be best practice in these situations?

    Re: Days/months Reversed On Copy

    I think the answer to this issue may lie in how the destination cell is interpreting the date.

    There are several option under Format > Cells... > Number > Date

    I believe the default setting is for one of the options marked with an * which means the date order changes depending on operating system settings (Localisation). There may be a difference between the OS date format of the .csv and the OS date format of the Excel.

    Have a tinker about with the different date settings. I think that may be the solution.

    Re: Determine Which Control Initiated Event

    StephenR - Thanks for the reply. You actually answered a question I had not asked yet so you must be psychic :)

    Dave Hawley presented a solution that has worked out just fine although I still wonder if there is a way to avoid having to hard code the name of the control in the procedure.

    It seems to me that since the procedure 'knows' which control it is for there should be a way of using that information. Would certainly be a very elegant solution if one existed.

    Either way thanks for the replies, my problem is solved.

    Re: Determine Which Control Initiated Event

    Thank you for the fast reply.

    I am now wondering if there is a more automated solution than passing a 'hard coded' string as a parameter and using Controls(strCntrlName)?

    Since the event procedure uses the name of the control, I was wondering if this existed as some kind of identifier when within the procedure, in a similar way to Me. for a Form?

    I have a UserForm, with over 100 controls, that is used to enter data into an Excel Worksheet. This includes a 'bank' of CommandButtons. As part of making this easier for the user, these have TakeFocusOnClick set to False.

    This currently results in a lot of repetative code under the CommandButton[COLOR="Blue"]1..20[/COLOR] _Click() events.

    Since TakeFocusOnClick is disabled ActiveControl does not return the CommandButton that initiated the event, but returns the ComboBox that I wish to keep focus since this makes it much faster for the user to enter data.

    Is there a way to identify which CommandButton has initiated the Click event without using ActiveControl? I would want to pass that identifier to a procedure to process the Click event.