+ Reply to Thread
Results 1 to 11 of 11

Selection of cells upto the last filled up cells in a column

  1. 06-28-2013, 12:40 PM #1
    Sam9
    Sam9 is offline
    Registered User
    Join Date
    06-28-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    27

    Selection of cells upto the last filled up cells in a column

    I have a workbook in which cells B1 to B3 are protected. The cells B4 to B2000 are filled up depending on the formula which is copied down to B2000. The Cells are filled up in continuation from B4 to down but every time the last filled up cell is different. Some time it is filled up up to B50 or B83 or B1000 etc. To select data upto the last filled up cell of column B, I can use the macro Range("b4", ActiveSheet.Range("b4").End(xlDown)).Select and this is perfect if there is no formula in column B but if the column B has some formula which is copied way down for e.g B2000 and the data received is up to e.g B50 this macro instead of selecting upto B50, is selecting upto B2000 even the cells after B50 are blank. I presume that excel is treating the formula in that cells also as a filled up cells. Please advise any other macro or command to select upto the actual filled up cells andd not upto the total cells where formula has been copied down. Thanks
    Reply With QuoteRegister To Reply
  2. 06-28-2013, 01:34 PM #2
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Selection of cells upto the last filled up cells in a column

    If I have read it right try...

    Please Login or Register  to view this content.
    or if the formula results are all numbers then you could try
    Please Login or Register  to view this content.
    Last edited by WasWodge; 06-28-2013 at 01:43 PM.
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown
    Reply With QuoteRegister To Reply
  3. 06-28-2013, 09:58 PM #3
    Sam9
    Sam9 is offline
    Registered User
    Join Date
    06-28-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Selection of cells upto the last filled up cells in a column

    Thanks WasWodge,
    I tried your first suggestion and it was the same thing which I needed. Could I ask something more?. Finally I needed that these selected cell could automatically be saved as a text file if we could slighty modify this macro.
    Reply With QuoteRegister To Reply
  4. 06-28-2013, 11:45 PM #4
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Selection of cells upto the last filled up cells in a column

    Could I ask something more?
    Strictly speaking it is a totally different question (it's not a slight modification, it's additional/new code) and so you should have started a new thread, but as you are a new member I will answer it this once but first of all I need something from you...
    What is the name of the sheet the code is currently running on?
    Last edited by WasWodge; 06-29-2013 at 12:46 AM.
    Reply With QuoteRegister To Reply
  5. 06-29-2013, 01:36 AM #5
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Selection of cells upto the last filled up cells in a column

    I am going out now and so I will leave you with this.
    First of all save your Excel file to the destination you want your text file to appear (the code needs it's filepath).
    Second change MySheet to your Sheetname.

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  6. 06-29-2013, 07:27 AM #6
    Sam9
    Sam9 is offline
    Registered User
    Join Date
    06-28-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Selection of cells upto the last filled up cells in a column

    This is giving text file with words #REF. I am starting a thread as advised by you(with complete details of this topic if total no. of words is not a problem). As I am new to the forum, I do not know how to rate the replies because I want to rate your reply the highest ranking. So next time please advise this also.
    Reply With QuoteRegister To Reply
  7. 06-29-2013, 07:32 AM #7
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Selection of cells upto the last filled up cells in a column

    Just add the bit in red below
    Please Login or Register  to view this content.
    Last edited by WasWodge; 06-29-2013 at 08:02 AM.
    Reply With QuoteRegister To Reply
  8. 06-29-2013, 07:58 AM #8
    Sam9
    Sam9 is offline
    Registered User
    Join Date
    06-28-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Selection of cells upto the last filled up cells in a column

    Yes, the data is a result of a formula that I have mentioned earlier also but I am confused because I marked this thread as closed and started a new thread as per your advise, then how this thread is open? Anyway thanks again
    Last edited by Sam9; 06-29-2013 at 08:02 AM.
    Reply With QuoteRegister To Reply
  9. 06-29-2013, 08:03 AM #9
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Selection of cells upto the last filled up cells in a column

    My post has posted out of order. Please see my last post #7.
    Edit: or actually it is just as easy for me to add the code again

    Just add the bit in red below

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    You can still post in a thread marked solved (its done that way deliberately), the "Solved" is just an indication to others to save them looking at posts. Threads can only be locked/deleted/closed by a moderator or Admin.
    Last edited by WasWodge; 06-29-2013 at 08:44 AM.
    Reply With QuoteRegister To Reply
  10. 06-29-2013, 10:08 AM #10
    Sam9
    Sam9 is offline
    Registered User
    Join Date
    06-28-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: Selection of cells upto the last filled up cells in a column

    Many Many Thanks. It saved my days. But again I do not know how to rate the replies but I am trying marking somewhere but for you all stars are less.(Excellent).
    Reply With QuoteRegister To Reply
  11. 06-29-2013, 11:36 AM #11
    WasWodge
    WasWodge is offline
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Selection of cells upto the last filled up cells in a column

    But again I do not know how to rate the replies
    You've done all you need to, you've said thanks and you have done what a lot of posters like and clicked the add reputation button. You don't need to do anymore.

    You will notice that jeffreybrown has locked the other thread and put in a statement about it being a duplicate thread.

    Basically when I stated that you should have started another thread (post #4) but said that I would carry on, and then the thread carried on for a few more posts then you should have just carried on just in this thread (others will still jump in if they see a different/better approach or obviously if they see any errors).

    Don't lose any sleep over it, because as a new poster you will get a fair bit of leeway (and most of us have had at least a suggestion made by a moderator, if not here then on other forums) but do please take some time to familiarise yourself with the rules (there is a shortcut button near the top of each page) and guidelines as it stops Moderators having to give infractions etc and makes for a smoother running of the forum.

    All the best and welcome to the forum.
    Last edited by WasWodge; 06-29-2013 at 11:39 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1