+ Reply to Thread
Page 4 of 7 FirstFirst ... 2 3 4 5 6 ... LastLast
Results 46 to 60 of 98

Finding last row and deleting beyond

  1. #46
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    hi jerry, im totally new to all this, arlette has kindly assisted with code, but its im still struggling with this, see how i have edited the fname line, im sure ive got it wrong!!!

    Please Login or Register  to view this content.

  2. #47
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    it doesnt let me select the csv file, also have i set the fpath at the bottom in the right way

  3. #48
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310

    Re: Finding last row and deleting beyond

    Kevin instead of clearing formulas from specific rows would it make more sense to import the cvs file first and then apply the formulas by macro only to the cells that contain data, triggering the macro by a worksheet change event?

    After each worksheet change event the macro clears all formulas then determines which cells in a column have data and applies your formula only to those cells.
    This way you could select all cells, clear contents (your formulas will be removed) then import your cvs file and your formulas will be reapplied only to the cells with data

  4. #49
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    thats sent my brain into overload

  5. #50
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310

    Re: Finding last row and deleting beyond

    Here is an example
    Paste the following in one of the worksheet's module

    Please Login or Register  to view this content.
    In a module named Module1 paste this code
    Please Login or Register  to view this content.
    You'll need to fix the IF statement (for one line of code) in macro1. It wouldn't paste correctly in the post

    Go to the sheet and enter values in cells of column A,
    the formula will be entered in column b.
    Clear contents of column A, the formulas will be cleared from column B

    You can also clear cells from the bottom up.

    The subroutine works with congruent data, you would need something more sophisticated for non-congruent data.
    So if you entered data running from A1 to A6 with A3 omitted, the formula would still be enter in B3.

  6. #51
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    if only i knew how to fix the IF statement!!! i have no code experience, by the way did you whole thread? to see what im trying to achieve?

  7. #52
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    jerry

    im getting a runtime error?? 1004

  8. #53
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Finding last row and deleting beyond

    In don't have a mac to test, just use the first version without a filter and select your CSV file.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  9. #54
    Forum Contributor
    Join Date
    06-27-2006
    Posts
    310

    Re: Finding last row and deleting beyond

    Quote Originally Posted by kevinless View Post
    if only i knew how to fix the IF statement!!! i have no code experience, by the way did you whole thread? to see what im trying to achieve?
    The IF statement has to read 'IF' thru 'Then' on one line,or needs a line continuation.

    To answer your question, I read just enough of the thread to know this would be very useful to you assuming you had some vba experience.

  10. #55
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    ok there was a colon after your choose file, which i removed now i can select a file, but now theres another error, see attached, i presume this is linked in some way?
    Attached Images Attached Images

  11. #56
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Finding last row and deleting beyond

    WHen you hover your mouse over fNAME, what value shows?

  12. #57
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    am i hovering in the module window, im not seeing any value???, sorry man , i know im crap! ask me anything on cs suite but not excel!!!!

  13. #58
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    i see

    fname = "alias Macintosh HD:Users:kevinlesser:Desktop:then the FILENAME

  14. #59
    @dministrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010
    Posts
    33,062

    Re: Finding last row and deleting beyond

    You may need to find an actual MAC forum to get this complete. You're asking blind and I'm Googling for you, you should be Googling...

    Found this thread: http://forums.macrumors.com/showthread.php?t=376881


    It posts 5 and 6 it seems to indicate you need to use an Open command, not Workbooks.Open. Just guessing.

  15. #60
    Registered User
    Join Date
    10-25-2012
    Location
    london
    MS-Off Ver
    Excel for mac 2011
    Posts
    75

    Re: Finding last row and deleting beyond

    i tried this on a pc but the end result still has the rows in it that had blank cells in column B, im not sure if this is because before the macro runs column B hasnt any blank cells but column C does??? i cant read the code so i dont know whats going in what order!

+ Reply to Thread
Page 4 of 7 FirstFirst ... 2 3 4 5 6 ... LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1