+ Reply to Thread
Page 2 of 7 FirstFirst 1 2 3 4 ... LastLast
Results 16 to 30 of 98

Finding last row and deleting beyond

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

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

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

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

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

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

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

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

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

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

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

    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.

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

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

    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?

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

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

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

    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?

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

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