+ Reply to Thread
Results 1 to 98 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
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    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

  16. #16
    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 was actually wondering also, would the code allow you to save the file as .csv once its performed the automation and save the filename as, say for example the day it was created? 25122012.csv or something like that???

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

    did you have any joy with this?

    thanks

  18. #18
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Quote Originally Posted by kevinless View Post
    here you are arlette
    Is this the raw data before running the macro?

    Quote Originally Posted by kevinless View Post
    i was actually wondering also, would the code allow you to save the file as .csv once its performed the automation and save the filename as, say for example the day it was created? 25122012.csv or something like that???
    Yes thats possible.

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

    yes thats the raw data, before the macro

  20. #20
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    So as per the recorded macro, the first step is deleting row1. Do you want to delete the header?

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

    yes headers are to be deleted

  22. #22
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    I made a few changes to the code and it runs fine from end to end.
    Please Login or Register  to view this content.
    If you want the formulae in columns E and N to be pastespecial values then replace this code
    Please Login or Register  to view this content.
    with
    Please Login or Register  to view this content.

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

    not sure what you mean by paste special values????
    all i need to do is get a default numeric of '1' in column E and default numeric "2" in column N, the only way i knew how was in my original macro, if you can code it a different way then thats great, unless you feel the way i have done it is OK then we can leave it as is????

  24. #24
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Paste special values is when you remove the formula from a cell and put in the value in it. Thats what i have done to the other columns other than E & N.

    So if you do not want to retain the formulae in those 2 columns, just follow the instructions in post 22.

  25. #25
    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 great, if a cell in column B was blank, could the whole row be removed?????

  26. #26
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Yes it can be done. Do you want this part (removing blanks from column B) to be added to the end of your existing macro?

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

    my thought it if a cell is blank in column B i dont need any of the information in any of the other cells so the whole line can be removed, yes if you can add to the existing macro so it does it all in one go that would be greaaat!

    what are your thoughts on the saving file with date???

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

    and to save out as a csv file!

  29. #29
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Updated code
    Please Login or Register  to view this content.
    Note: This code resides in the current workbook. However to ensure that everything works smoothly it will be good if you keep the macro independent of the workbook. So you can ask this macro to open the csv file for you, do the required steps and then save as csv.
    What do you say?

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

    thankyou for your patience! i dont get what you mean?!

    keeping the macro independent - i have exported a .bas file and would import that into the new workbook, is that what you mean?

    not sure about asking macro to open csv file, do required steps and save as csv, again dont understand this!, i can see once its run it names the file as the date, not sure where it saves it?

    also, can you run the macro on this raw file, its not deleting the row that has the blank field in it?
    Attached Files Attached Files

  31. #31
    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, the blank cells in column B after running code now have a duplicate entry from column A??, any ideas?

  32. #32
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Quote Originally Posted by kevinless View Post
    keeping the macro independent - i have exported a .bas file and would import that into the new workbook, is that what you mean?
    Instead of doing this each time, you can have the macro reside in an independent file and then ask the macro to call the file which you need to run the macro on. Just a slight change of code is required and i can help you with that.

    i can see once its run it names the file as the date, not sure where it saves it?
    You can provide the path where you want the file to be saved and i can enter it into the code.

    also, can you run the macro on this raw file, its not deleting the row that has the blank field in it?
    Will check this now.

  33. #33
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    I checked the macro. What do you mean by duplicated value from column A?

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

    Well, can I send you a screenshot? What type of file can u open?

  35. #35
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Any kind. Just upload it.

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

    Quote Originally Posted by arlu1201 View Post
    Instead of doing this each time, you can have the macro reside in an independent file and then ask the macro to call the file which you need to run the macro on. Just a slight change of code is required and i can help you with that.

    You can provide the path where you want the file to be saved and i can enter it into the code.


    Will check this now.
    Path would be just the desktop do you need a directory for mac desktop??

    Call the file sounds interesting?! Does the file always have to be the same name or would it work like a hot folder queue scenario?

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

    Quote Originally Posted by arlu1201 View Post
    Any kind. Just upload it.
    a before and after screengrab

    you can see in the original file C4 and C10 were blank, after macro they show duplicate, where if the line is deleted altogether, lines 15918 and NB*1221 should not appear at all?

    thnks

  38. #38
    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 arlette, did you have any luck with this issue? thanks

  39. #39
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Quote Originally Posted by kevinless View Post
    Path would be just the desktop do you need a directory for mac desktop??
    Call the file sounds interesting?! Does the file always have to be the same name or would it work like a hot folder queue scenario?
    I can give you a file open box that you can use to navigate for your file.

    Quote Originally Posted by kevinless View Post
    a before and after screengrab
    you can see in the original file C4 and C10 were blank, after macro they show duplicate, where if the line is deleted altogether, lines 15918 and NB*1221 should not appear at all?
    Do you want the rows in column B to be deleted before doing the rest of the steps? I thought its at the end and have added it to the end of the macro.

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

    open box would be good. i would say say yes delete rows deleted then do further steps, basically so it takes the rows that have no cell data in B totally out of the equation when i save the file after the macro has run

    thanks

  41. #41
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Try this code
    Please Login or Register  to view this content.
    It includes the popup to open the file, the blank deletion part and even saving the file. Change the section marked bold to match your system.

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

    seems to be a slight issue here???
    Attached Images Attached Images

  43. #43
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    I tried it at my end and it works fine. But i am using a windows 7 PC. So maybe thats whats causing the issue at your end, since you are using a MAC. Let me push this over to a few of our MAC experts and see what they say.

  44. #44
    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 thanks arlette

  45. #45
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Finding last row and deleting beyond

    Getopenfilename is not a mac function that works. Use the Applescript equivalent:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    _________________
    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!)

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

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

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

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

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

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

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

  53. #53
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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.

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

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

  56. #56
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Finding last row and deleting beyond

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

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

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

  59. #59
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    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.

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

  61. #61
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    I just checked the file you uploaded in post 30 and it should be column C. So your updated code is -
    Please Login or Register  to view this content.
    Last edited by arlu1201; 11-01-2012 at 10:32 AM.

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

    ROCK N ROLL!!!! well done you - thankyou so much, final request now??> how do i type the fpath for a file to go on the desktop on windows? and also is there a code that can automatically email the csv file???? !!!

  63. #63
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    The desktop path varies from user to user. I would suggest that you just record a small macro of saving a file (any file) to the desktop and you will get the syntax with the complete path of your system.

    You will need another small bit of code to email the file but i have never worked on that bit. You should find it in this forum because there are many such questions asked. If you dont, just create a new thread for that.

  64. #64
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Finding last row and deleting beyond

    Here's a trick for getting the Desktop address into an fPATH string:

    Please Login or Register  to view this content.

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

    thanks JB and arlette makes sense will do that

  66. #66
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  67. #67
    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, pls run macro on this attached file, see line 19 selkirk foods, it dissapears on final csv after running macro, this line has a blank cell in column B but not C??? i would have expected it to stay in file as it has data in C...

    let me know what you think pls?
    Attached Files Attached Files

  68. #68
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    There was some error in the code in post 61 and i have corrected it. Please try now.

    I would suggest that you keep both the Mac and the Windows commands in the same code. Just use the single quote (') to indent what code you do not want to use. So, if you are using the MAC to run it, before running the code, indent (put the quote) in front of the windows code lines and vice versa. This will save you the headache of maintaining 2 codes (incase there are changes).

  69. #69
    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 better thanks, why are some columns indented slightly?

  70. #70
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    I didnt touch the formatting. Maybe the cells were formatted that way in the file.
    If you want to change the format, you can do that via code, just specify what you need done.

  71. #71
    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 arlette, is there a code we can add to say any cells with no info in, just clear them, i know it sounds odd, but when ive run macro and saved a csv it still doesnt import into sage, but if i clear the cells manually beyond the last row, then resave it imports, but that defeats the object!!!, so, if i can maybe somehow re-delete or re-clear any empty cells in the whole worksheet this might just do it????

    thanks

  72. #72
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    You want the macro to check for the last used row and last used column and then delete all columns and all rows beyond that?

  73. #73
    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 thought it was doing that already? Is it not!

  74. #74
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Though thats the title of your thread, we were editing your main code and didnt do this part.

  75. #75
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Is there any fixed last column? Or should the macro check it?

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

    The last column will always be the column that has the "2" in it. I think it's column n

  77. #77
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    You can put these 2 lines before the End With statement.
    Please Login or Register  to view this content.
    However, you will observe that it does not actually delete the columns n rows. In that case, you can hide them.

    So you can change the above code to
    Please Login or Register  to view this content.

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

    thanks, will try this tomorrow, what is the purpose of delete statement if it does not delete?!!

  79. #79
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    In the case of a delete, what actually happens is that the column to the right replaces the existing column. We see this clearly if there is data in the columns. But we dont, when they are blank. Hence, it appears as if the columns are not being deleted.

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

    just ran macro, slight error?
    Attached Images Attached Images

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

    just tried this, wont be able to tell until i import into sage tomorrow, but it doesnt error?
    Please Login or Register  to view this content.

  82. #82
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Oops i am sorry i got confused.

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

    still not importing arlette, im sure its something to do with the way its saving the csv file???? is there another way we can save it as csv? is it saving it as just a text file and adding.csv on end in your code?

  84. #84
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    The format is clearly specified as xlCSV. Are you automating the exact steps that you do manually?

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

    just spotted something else, which may be the problem? can you try run it on this excel file and see if there are entries in column N and E on top line after running macro
    Attached Files Attached Files

  86. #86
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    I get blanks in E1 and N1 after running the macro.

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

    so i think this might be the problem, when i import into sage there must be values 1 and 2 in e and n, do you know why there is no value in E1 and N1???

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

    ive sorted the problem with the mac to windows issue, ive just made the file format FileFormat:=xlCSVWindows, just if you can assist with the blank cells in e and n1 would appreciate.

    thanks arlette

  89. #89
    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 figured it out!!
    Please Login or Register  to view this content.
    this will use column D instead of C!

  90. #90
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    So is anything else pending to be solved?

  91. #91
    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, if i wanted to leave the filename as it is and not rename it as the day/month/year it is created after running macro, how does the code need to change pls? i will still want .csv on the end though
    Please Login or Register  to view this content.
    thanks

  92. #92
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    You mean you just want to save the file and not a saveas?

  93. #93
    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 suppose yes, that will save the file in its original name correct?

  94. #94
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Right. You need this line
    Please Login or Register  to view this content.

  95. #95
    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 kind of figured that!! but the fname and fpath i seem to be struggling with, doesnt like it, see post 61 for full code, it still needs file format csvwindows, but i cant work out how to delete the dd/mm/yy part in the fname and get it to run

  96. #96
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    You are saving the file with its original name in the original location, right?

    Then you just need Activeworkbook.Save. What error are you getting?

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

    so when i open the file when i run macro on mac, and i need to save as windows csv file but with the same name in same location, do i need to add anything to code other than Activeworkbook.Save.??? i tried this
    Please Login or Register  to view this content.
    but it gives syntax error on that line

  98. #98
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Finding last row and deleting beyond

    Oops i am actually not sure about this part. I have never worked on a MAC. Let me try and get some experts to assist you with that.

  99. #99
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Finding last row and deleting beyond

    you can't specify a format if you just save. if the current format is xls then
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

+ Reply to Thread

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