Posts by RickWHaas

    Re: Automatically Delete Certain Sheets Upon Saveas

    This worked perfect. The only thing I had to change was the tab reference. The tabs actually have different names other than 1, 2, 3... so I reference just the sheets(1), sheets(2)... I also added two lines to make it go back to the first tab instead of the last which was getting really annoying. The code is below.

    Again, you guys are great

    Thanks for your help.

    Our company has designed a Excel Document for quoting purposes. The problem we are having is that certain sheets that are needed to do the quote do not need to be used after the quote is finished and saved. These extra worksheets are taking up a lot of room on our server.

    In the document I have already disabled the save feature so that the saveas has to be used. Is there code that can delete sheets 3, 5, 6, 7, 8 upon doing a save as?


    Re: Copy All Numerical Values From Column

    I was actually able to add a text to columns into my macro that separated the columns by a plus sign. This removed the plus sign and I havn't seen any problems yet.

    I'll try yours too just to see if it calculates it quicker as there are A LOT of rows for this to run through.

    Thanks for your help

    I have a list of numbers in Column A that I always copy into another sheet. The problem I have is that some of these numbers end in "+" and I can't use them when it has that. I have to go through and one by one take out the plus. I was wondering if it was possible to copy only the numerical values to a new column?


    Original What Im Looking For

    4 4
    56 56
    8+ 8
    5 5
    90 90
    2 2
    4+ 4
    45 45
    1 1
    67 67

    Thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Actually I just figured it out. I did a text to columns with a + sign as the separator and it worked.

    Thanks to all who looked at it.

    Right now I have a column that has 1000's of numbers that has been autofiltered. When I filter to show only numbers higher than 10 I want the total sum of the line to then add only the numbers shown


    Numbers Filtered Numbers
    3 17
    17 49
    8 57
    4 Sum 123

    Sum 138

    Right now when I filter I see the same summed number before and after you filter the line.


    Re: Find Word In Column, Set Formula Based On Response


    Thanks for the quick reply, and the formula would work great if the cell only contained "Reservoir" but here's an example of what one row would look like containing reservoir

    "Reservoir,50 Gal,JIC Type Style A,36 Long X 24 Wide X 23 Tall"

    I have to be able to search for "Reservoir" that is inside otehr text in multiple rows.
    When I go to edit - Find it works perfect in finding "Reservoir" but when its not there I get errors.



    I would like to use the following code to see if the word "reservoir" shows up in any of the rows in a certain column. If it does I want to insert the formula =SUM(D7:D257)*0.1 into cell C2 and if it doesn't then I want to put a 0 into cell C2. I've tried many routes but can't get past the error if it can't find the word.



    I have a LARGE list of items in rows with a quantity column next to them. I want to be able to popup a message box when the user enters a numeric quantity in the quantity column. There will be numerous messages for different rows.


    Column A = quantity
    Column B = Description
    Column C = Part Number
    Rows 3-1500 have different items

    Rows 8-15 will have the same message for the parts - "This item requires adaptor"
    Rows 29-172 will have the same message for the parts - "This item comes in a qty of 2"
    Rows 723-812 will have the same message for the parts - "Please choose appropriate bolt"

    Thanks for your help!

    In my program I have prevented the user from saving the Workbook with the following code

    I also need to prevent the user, when they use the save as, from saving certain worksheets.

    Ex: There are 6 worksheets in the workbook. I only want them to be able to save the first three.

    Issues: One of the workbooks that I want to allow them to change has a tab name that changes frequently.


    I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want.

    Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet.

    My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item.

    Issues to consider:

    -Line item 1 may not always be the top item. (because the user may not choose the top item)
    -There will be spaces inbetween the line items since the user may not pick all of their chosen items in order.
    -Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)

    This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references

    Any help or suggestions would be greatly appreciated

    Re: Find Last Row Containing Text

    I actually had two rows that I could use to count, one with Item numbers and the other descriptions so I actually ended up using code from PCI and RichardSchollar to make it work

    Here's my code for any one needing it

    This was actually used to find the last row with data in it so that I could set a print region in a sheet that changed everytime it was used

    Thanks for all your help

    Re: Find Last Row Containing Text


    Sorry I've been gone all week so today is the first day I've been able to work on my program. I've found that the last two help the most when used in the macro so I had to go with that. Thanks for all your help it works perfect.

    This may be easy but I'm completely stumped right now.

    I'm writing a macro that sets print areas automatically from a button. There's one sheet that the rows will change everytime, anywhere from 10 lines to 250 lines.

    Is there code that will find the last row containing text?

    I'm looking for a way, inside a Macro, to delete any value in a range of cells that is not a whole number.

    Say I have a range from "A4:C50" I am pasting items from another sheet through a Macro Button but I want code at the end of the Macro to then delete anything in this range that is not a whole number.


    Re: Display Msgbox When Item Selected From Combo Box


    That is one reason but I also need to display more than 8 list items at a time and I would also like the ability to change the font of the list. Any suggestions? I'll make a new thread if you think you can help with this.


    Im using code from website:

    to display a combobox when I double click any cell that contains a validation list in it. I'm not great with VBA so I am having problems finding what each part of the code does. This is causing problems for another bit of code that I use to display a msgbox when certain values are selected from the list.

    The MsgBox shows up great when I select an item from the validation list but does not work at all when I select the same item from the double-clicked combo box.

    It would be great if I could get the MsgBox to work both ways.

    This is the code that displays the MsgBox when target value is selected from list

    Private Sub Worksheet_Change(ByVal Target As  Range) 
        Select  Case Target.AddressLocal(False, False) 
        Case Is = "B4", "B5", "B6", "B7", "B8", "B9", "B10", "B11", "B12" 
            If Target.Value = "H" Then 
                 MsgBox "This is a pop for extra information. Savvy?", vbYesNo, "Yo!" 
            End If 
        End Select 
    End Sub

    This is the code that displays the ComboBox when I double click a validation list cell


    Re: Display Popup When User Picks Item From List

    I got this code from

    You can double click on any cell on the worksheet "BOM" that has a validation list in it and it will pop up the combo box so that you can view more lines than the 8 that a validation list gives you.

    If you want I can send you the excel file. Its too big to put on the site.

    Thanks for you help