Posts by Derick

  • [Solved] Row height controlled by cell value

    Hi Derk,


    Thanks very much for your code. This works well when one enters the values and then presses enter one cell at a time. Can I adapt it to automatically take whatever value is in the cell, whether that be from a calculation on another column, pasting all values in at once etc?


    Thanks and regards,


    Derick

  • [Solved] Row height controlled by cell value

    Hi All,


    I am wondering how I might control row height using a value in say the first cell of a row? Then if this can be done, would it be static or could it be dynamic - ie. if I change the cell value will the row height automatically change?


    Thanks,


    Derick

  • Calling Excel based diagrams

    Hi Andy,


    Thanks for your reply. The diagrams are simply made using the draw functionality in Excel (same as in Word etc) - ie. lines, shapes, fills etc. I guess they are not images as such so I am not sure how one could identify them as an object on the sheet. I will email an example to you. Using Excel 2000.


    Thanks and regards,


    Derick

  • Charts : Highlighting data selected on a graph

    Hi All,


    Wondering whether anyone has experience / ideas on how one could select a region of data points on a scatter plot (eg. somehow highlight the area with the mouse or frame an area with sliders), and then these data values on the sheet would be highlighted or shaded. This data could then be either removed, manipulated, copied etc.


    Thanks,


    Derick

  • Calling Excel based diagrams

    Hi,


    I have a series of diagrams drawn in Excel and am wondering whether it is possible to somehow name them, then call them up from the sheet they are on to another and place them in a certain position / order. It would basically be building a large diagram using selected smaller diagrams / components according to which component diagrams the user specifies.


    Thanks,


    Derick

  • Delete rows based on data in non-fixed column

    Hi Derk,


    Tried your code and it works very well - thanks!


    I'd like to also use it in the same way to find a particular heading text (column), but delete rows when the number in the column is negative. Do I make


    What:=s


    and set s to be a variable which is < 0 ? Have searched the VBA help but not sure on what exact notation to use, if this is what I should do.


    Thanks,


    Derick

  • Resampling a dataset, formulas, VBA?

    I have made a spreadsheet which allows the user to resample a set of data. I am hoping someone can advise as to the best way to streamline my spreadsheet so only the shaded columns are seen or present on the sheet.
    I don't see the file attachment button anymore - so please pm or email me if you would like a copy of it.
    Is there maybe a way to do what I have with VBA which is more efficient and user friendly? Or is it easir to simply transfer the columns with the calculations to another sheet and hide the sheet?


    The user pastes in depth (or time - see Time sheet) and associated data in columns P and Q. The new depths where one would like to know the data value (exact match if present or linear interpolated value between the two nearest matches) are entered into column B. Columns C to N are not necessary to show on the sheet, and the new data value is in column O. The way I have it may be a little confusing; first it finds the position of an exact or next lowest depth match, and the next cell (next largest depth match). Then the relative position between these two depth values of the new sampling depth is found as a ratio. If the data associated with these two matches is increasing in value it adds the ratio x difference in data values to the lower data value, if decreasing it subtracts same. Graphs show the original and new sampling.


    Cheers,


    Derick :)

  • Delete rows based on data in non-fixed column

    Hi Weasel,


    Thanks for your reply. Yes the criteria column will always have the same heading and this column will always be present, but its position will not always be the same (depending on how the data is copied from another application). The criteria column data will simply consist of the numbers 1 and 2. If the number 2 is present, the row (preferably C:AF) should be deleted, including the 2 in the criteria cell/column.


    Regards,


    Derick

  • Delete rows based on data in non-fixed column

    Hi All,


    I am trying to delete rows (or part of a row) of data which may have a number (say 2, among other numbers) in a particular criteria column. The heading row (row 6) has for example the word "criteria" in one column, but this column is not in a fixed position. Ie. someone may put this criteria data in any column C to AF.
    There are many good posts dealing with deleting rows, but I am unsure of the best way to find this criteria column first. I also then (after bad data rows are deleted) want to return the min, max, and average values for all the data in each column, in rows 3, 4, and 5. I am not sure if some deletion and / or filtering methods simply hide these rows and whether these hidden rows will then still be falsely used in the min, max, av calculations. My datasets can be 10000s of rows x columns C to AF. Is it also possible to not delete and cells in columns A and B as I have some other inputs there (but these could be moved if speed is best deleting entire rows)?


    Cheers,


    Derick :biggrin:

  • VBA: Userform - option buttions to call various macros

    Hi,


    I am trying to make my first userform and have a few Qs. Basically I have another macro that reads data into Excel, then depending on the users selection of option buttons on my userform (pic attached - tried but does'nt seem to attach, are .jpg files ok?), another macro is run to process the data. My userform says; This A1-A3 (data layout type) contains B1-F1 (data present).


    In the properties for each option button I have assigned them to a different group according to their letter. I could not work out how to use multiple frames, and don't know which method is best? I want the option buttons to perform as follows;


    - the user must select only one of either A1, A2, or A3, with default at A1
    - the user can but does not have to select B1, default is on
    - the user can but does not have to select, C1 or C2, or none, but not both
    - the user can but does not have to select D1
    - the user can but does not have to select E1
    - the user can but does not have to select F1


    I do not want the user to be able to proceed unless at least one of B1 - F1 has been selected (as they could unselect B1).


    How do I then call another macro/module if a particular combination is selected. Ie.;
    if A1 and B1 are selected, then run sub_1
    if A2, B1, and C2 are selected, then run sub_3 etc
    I so far have the following, but not sure if it is on the right track and how to set the Cases so multiple/combinations of option buttons will be used.


    ---------------------------------
    Sub Show_Configuration_Form()


    'set initial option button values
    UserForm1.OptionButton1.Value = True
    UserForm1.Show
    If Not bResponse Then End
    'determine which button was selected
    With UserForm1
    Select Case True
    Case .OptionButton1.Value:
    Run sub_1()
    Case .OptionButton2.Value:
    Run sub_2()
    Case .OptionButton3.Value:
    Run sub_3()
    End Select
    End With
    Unload UserForm1

    End Sub
    ---------------------------------


    Thanks in advance,


    Derick :)