+ Reply to Thread
Page 1 of 7 1 2 3 ... LastLast
Results 1 to 15 of 98

Finding last row and deleting beyond

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

    Finding last row and deleting beyond

    All,

    i have a macro that ive created using record function, as new to macros! i have an IF formula in two columns, when i record the macro, it inserts a "1" or a "2" in specific columns based on whether there is any cell info in column A, i have option copied this formula down to 1000 rows, But if my spreadsheet is only 100 rows long, the macro still creates the IF formula from 101-1000 and formula is still in the cells, when i save as csv file and try to import into sage it errors, only by manually selecting cells beyond last row and deleting then re-importing into sage does it import.

    so i think im looking for a code that detects last row (inc formulas) then deleting below it

    can anyone assist?

  2. #2
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Finding last row and deleting beyond

    You can actually find the last row and then put the formula only till that row, so you do not need to delete them afterwards.
    Can you please post your code here? So i can help you put in the lastrow part.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    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 to post the code, but i had a message back saying it did not comply with rules?!

  4. #4
    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

    arlette, i attach it as a zip, is that good?
    Attached Files Attached Files

  5. #5
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Finding last row and deleting beyond

    Yeah you can post the code here, as long as you put it within code tags. To do so, copy paste your code here, then select it and click on the # button on the toolbar. And submit it.

  6. #6
    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 also attach my csv that i used to run macro on
    Attached Files Attached Files

  7. #7
    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

    you can see when macro runs, past row 8 it still has formula beyond 8, this i think is where it does not like import into sage, because when i delete manually all cells below 8 it imports

  8. #8
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Finding last row and deleting beyond

    I am having a problem with zip files on my system. Can you please post your code directly here?

  9. #9
    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

    Please Login or Register  to view this content.

  10. #10
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Finding last row and deleting beyond

    Your code had too many select statements. So i re-wrote your code as follows - See if works for you -
    Please Login or Register  to view this content.

  11. #11
    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

    this is much faster thanks arlette, BUT it is not duplicating the formulas beyond line 2 in column B, E and N??

  12. #12
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Finding last row and deleting beyond

    Actually i did the macro without looking at your file and i assumed that column A is the column with the maximum entries.

    Dont B,E and N have the same number of rows as column A?

  13. #13
    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

    they do have same number of rows, but when i run your code i only get data on line 1 in column B E and N all other columns have correct entries.

    ???!!

  14. #14
    Administrator arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,145

    Re: Finding last row and deleting beyond

    Can you please upload a sample of your file (do not zip)?

  15. #15
    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

    here you are arlette
    Attached Files Attached Files

+ Reply to Thread
Page 1 of 7 1 2 3 ... 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