Posts by Joe Derr

    Re: Trim last name


    Thank You!

    Man I love this forum.. thanks everyone that has helped me in the years past..

    snb, thanks for the quick reply, that did it..


    Hey all,

    This code below works as advertised, but when there is only the last name only already in the cell, it basically trims that so it ends up a blank cell.

    strName = Trim(Cell)
            lSpace = InStr(1, strName, " ", vbTextCompare)
            Cell = Trim(Left(strName, lSpace))

    So the cell contains: (just the last name)


    it will trim it to a blank cell

    If the cell contains: (last and first name)


    it will be trimmed to:


    Which is correct..

    Is there a modifcation of the code to detect nothing to the right of the first space and then leave it as is.. if there is anything to the right of the first space, then it trims it out?

    Thanks in advance!


    Re: Extract numbers after dash

    One Last Thing.. as I was running the code (awesome code) I noticed an issue..

    Is there a way to determine a new month and start over from 01 rather than continuing to count up?

    20110131-156 31 Jan 11
    20110201-01 01 Feb 11

    I just thought of it as I ran the last data from the 16th to, today.. I dont even know how to T/S your line of code or I would take a whack at it...

    Sorry to be a pain, your code is awesome!


    Re: Extract numbers after dash


    Awesome!! That did it.. I would have never be able to come up with that myself, thank you. I have put commented in a credit to you in the file for your fix to this problem.

    Thanks again!!


    Re: Extract numbers after dash


    Thanks for that code... it does the last digits as advertised, but the date in Column B has to factor into the first part of the number..

    Column A in the 1st row is 20110203-123 Column B is 3 Feb 2011
    Column A is the 2nd row is blank, Column B has the date 5 Feb 2011

    I am looking to read in the date from column B, convert it from MMDDYYYY to YYYYMMDD

    then combining the last digits past the dash from the row above... so it would be like..


    If it's possible.. thanks for the help....

    I have tried multiple right, left and trim functions, but everything I found is hard coded to last two or last 7 digits.

    I have a date formated tracking number and I want to increment it by one, however the date might be different from one line to the next, but not always.

    Col A Col B

    20110201-15 01 Feb 2011
    20110201-16 01 Feb 2011
    20110202-17 02 Feb 2011
    20110203-18 03 Feb 2011
    20110203-19 03 Feb 2011
    20110203-20 03 Feb 2011
    04 Feb 2011
    04 Feb 2011
    04 Feb 2011
    05 Feb 2011

    What I am looking to do, is increase the number, by extracting the number after the dash, and adding it to the converted date, then writing it to column A until there isn't a date in Column B, indicating the last line of Data. I guess a Do until Nothing? or ??? The next 4 will be


    See the attached test book.. It doesn't work, I can get the date read and converted, but I can't get just the last digits.. they will vary from two to three digits. Like I said all the examples hard coded 2 or 7 digits..

    So some of the numbers in Column A could be like



    Thanks for any guidance..

    Re: compare sheets delete matchs

    Ran into a problem implementing the code.

    On my sheet 2, I have data from A to I with Columns A and H being blank (ready to accept other code down the line)

    When ever I run the code, it errors as soon as I put even one line of data on Sheet 2... I see this part of the code from above

    wsSheet2.Range("B1:C" & lngRows2).AutoFilter Field:=2, Criteria1:=1

    Is there a way to use another column for temp info.. like Column A or H or even anything past Column J ?

    Re: compare sheets delete matchs

    Brilliant !!
    Thank you for the help, that is EXACTLY what I was wanting it to do, and even did the copy bit too. You are outstanding! Its funny that I can search myself and not come up with anything close, but others can. Even the suggested topics didn't pull that out. Thank you again, I will make reference in the code to both you and Batman (the other link) for the coding.


    Re: compare sheets delete matchs


    Thanks for your reply.

    The next step would be to copy the remaining rows of data to the next open column in Sheet1. I didn't include that, because this part was already daunting enough, seamingly.

    The file is of course what I have, and it's broken. I included more detailed narrative on the sheets, and when you launch the user form, and press the button, nothing seems to happens, but what is going on, is there is a continous loop... it never ends, so you have to hit CTRL and Pause/Break to break out of the loop, then you goto the code hover over the variable "iCtr" and you will see you stopped at some number along the way.. it just doesn't work..

    Thanks for looking.

    Hi All,

    I have looked and found tidbits of code but can't seem to get anything to work.

    I have two sheets, for simplicity they are Sheet1 and Sheet2
    In Column B I have numbers, not sequencial, but sorted from lowest to highest.

    Sheet two is populated via a database that exports to excel
    Column B is the numbers again, but there might be more of them.. as the database is used, the numbers increase..

    From Say.. Jan 1 - Jan 26 there could be 50 numbers, on the 27th 5 more entries, then nothing until the 30th when 10 more are put in.. from the last export, on the 26 where there were 50 entries, now on the 30th when I run the "report" if you will, there are 15 more. However the report will pull all 65 reports.

    What I am trying to figure out how to do, is loop through Sheet2, read in the first number in Column B, then look in Column B of Sheet 1 to see if it's already on the sheet, that is the tracking sheet. If it matches, then I would like it to delete the row entirely on Sheet 2 (the imported sheet from the database) then loop to the next number, on Sheet2 Column B, look at sheet 1, Column B and see if that one is there.. yes... delete that row from Sheet2 etc until the end.

    I have tried to combine find, with delete and just about deleted my whole project :(

    I need it in excel VBA, I found a vlookup but it was sheet based.

    Thanks in advance.


    Re: Last Sheet Name Closed Workbook

    Cytop, AAE, and Clarity,

    Thanks for your help, Cytop's code fit perfectly. I appreciate everyone's help, I wasn't sure if this was ever going to work right.. AAE, I kept seeing how to find the last worksheet of the workbook I am in, but never anything from another workbook, just what tabs are in there at best... Again Thanks for all your help, Clarity too.. I bookmarked that page for future reference, thanks for pointing it out.



    Re: Last Sheet Name Closed Workbook


    Thanks.. I will look over those.. However, as my search has led me in circles... I keep finding bit's and pieces that start to work, but then error.. ADO, checking to see *if* there is a certain sheet, but nothing that will say, ok Joe.. you browsed to this file, good.. here is a list of Sheets and this is the last sheet... storing it as a variable... as such, I need to use it in this part of the code..

    Specifically this part:

    ' read data from the source workbook
            .Range("A5:Q12").Formula = wb.Worksheets("OCT 26").Range("A54:Q61").Formula
        End With

    The hard coded OCT 26 needs to be whatever the last tab is in the workbook that I select.. time marches on..

    If anyone has found anything that works.. then please let me know.. I will post if I ever figure it out.. there has to be something somewhere... I just cant find it...


    I have tried a billion ways (it seems anyway) to browse to a workbook, and then extract data from it, but to do that, I need to know what the last worksheet is.. it is variable (Daily) product that is created by another department and sent to us for analysis. What I am trying to accomplish, is it has tons of extra data and now, I delete the rows of un needed data and manually manipulate the data.. What I want in the end, is press a button on my file, I will browse to the saved .xls file, and the program will look into that worksheet, find the last sheet, then extract data from a range and import that to my worksheet for me.

    What I need to know what is the code that will look into the workbook I select and find the last tab, again it changes on a daily basis.. They create the excel file, so I can't load any code on that side to help. Is this possible?

    Here is the code I have thus far to open browse and give me the path, and file name that I selected, I am sure I will need that along the way.. Thanks for your help!

    Re: Target Row Height


    You are right, I did create a new test workbook to test your code, independently of my workbook and, as you said.. it did work. I will see if I can find anything that is keeping it from working correctly. Thanks for your post.

    UPDATE: I realized I didn't set my rows to 21.75 and when I ran the test, it reduced it back to 15.00.

    Scratching head.. any ideas?

    Re: Target Row Height

    Thanks for the reply, I can't seem to get it to work, after I delete the content out of the row..

    IE. It expands to except the long text but when I delete the text, it just returns the line to 11.25. I would like it to be 21.75 if possible.

    This is just to return the lines to the normal height of 21.75 if there was an erronous input.

    Is there a way to detect the delete key in the target area?

    Hey all,

    Trying to get the code below to work. I have it set to expand when long text is inputted (it is set to word wrap) however when I delete the text, I would like it to set the row height to 21.75.

    This code is inputted into the sheet object, so it's really not a function.

    ' ===============================================================
    ' This function automatically resizes the Descrepancy column H
    ' to expand to fit the whole write up.
    ' ===============================================================
        If Target.Column = 8 Then
        End If

    I tried this but it still reduces the row to 11.25.

    I haven't alot of experience using Target, so any help would be appreciated.

    Re: Insert row with formulas conditional formating and data validation


    I tried your code, but it still did it, looked like it was going to insert a line but still only ended up with 3 lines.

    Maybe I need a way to:
    * Insert a line below.. I have done this already..
    * Then it will need to copy the formulas, data validation, and conditional formatting that is unique to each row via VBA maybe???
    * Re-align the rows so they are sequencial down to 150 for each column.

    These lines can be inserted anywhere from Row 10 to 150, there are prefilled boardered lines in that whole range.

    I will see if I can break down the workbook so I can send in an example..