+ Reply to Thread
Results 1 to 18 of 18

end of data in a column

  1. #1
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    end of data in a column

    hi, i have a sheet that links to data in another sheet. the links go to many more rows than there is data. i want to get to the bottom of the data but not to where the formula ends, just to where the data ends. the Selection.End(xlDown).Select goes to the end of the formulas. how can i go to the end of just the data and not to where the formula ends?

    in the sample below if i use the selection.end code above i'll go to the last zero since that's where the last link to another sheet is, but i just want to get to the row that shows link_item3.


    link_item1 (these rows link to another sheet)
    link_item2
    link_item3
    0
    0
    0
    0

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: end of data in a column

    use r to get the row number, use rng to the the cell.
    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 02-15-2019 at 08:49 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    I'm kind of new to this so not sure if I am doing something wrong but I get a syntax error in red when i put the above code into a macro. what else do i need to do if the data starts in cell a2?

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: end of data in a column

    Sorry for the typo. Had a missing quote mark.

    Please Login or Register  to view this content.
    These are examples of how to get the last cell with data in a single column, using column A as a reference. The characters in red font would have to be changed for each different column. It ignores formulas and only looks for cells with visible values. It searches from the bottom up to avoid giving false readings by blank rows between groups of data.
    Last edited by JLGWhiz; 02-16-2019 at 09:14 AM.

  5. #5
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    i'm still not getting this to work. in A1 i have the header name and from A2 to A21 i have data, but only rows A2:A6 have visible data in them, adn rows A7:A21 have 0 because they are linked to another sheet where there is no data in those rows. if i am only using col A and have 21 total rows of data including the header row, do i need to change the "1" that you currently have in red?

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: end of data in a column

    Can you attach your file, or a facimile of it? The original question is very general and I tried to give an example based on one column, But there are all kinds of circumstances that can cause variations to be needed in the code and I do not want to do a tutorial on this thread. To attach a file, click the 'Go Advanced' button at the bottom of the reply box, then click 'Manage Attachments' and follow the menu to upload your file to a share sever for this web page.

  7. #7
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    file should be attached. thx.
    Attached Files Attached Files

  8. #8
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: end of data in a column

    Change the formula in column A to
    Please Login or Register  to view this content.
    and drag down to get rid of the zeros then the code will work.

  9. #9
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    still couldn't get it to work. file attached with the formula change.
    Attached Files Attached Files

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: end of data in a column

    The code is returning 6 as the value for r and "Abraham Lincoln" for the range value, which is cell A6. What were you expecting to see as a result?

  11. #11
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    oh. maybe this is much simpler then. i just want the cursor to physically go down to cell a6. if i am in cell a1 and i hit the end-down arrow it goes all the way to a20, but i just want the cursor to physically go to where the visible data ends and that's a6.

  12. #12
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    oh. maybe this is much simpler then. i just want the cursor to physically go down to cell a6. if i am in cell a1 and i hit the end-down arrow it goes all the way to a20, but i just want the cursor to physically go to where the visible data ends and that's a6.

  13. #13
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: end of data in a column

    Quote Originally Posted by charliec View Post
    .......but i just want the cursor to physically go to where the visible data ends and that's a6.
    Just add the following line before End Sub

    Please Login or Register  to view this content.
    .

  14. #14
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    thanks Haluk. that works. Also thanks for all your help JLGWhiz. Very helpful.

  15. #15
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: end of data in a column

    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  16. #16
    Forum Contributor
    Join Date
    12-02-2010
    Location
    USA
    MS-Off Ver
    Excel 365 and sometimes excel 2013
    Posts
    172

    Re: end of data in a column

    thanks bakerman2! works great.

  17. #17
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: end of data in a column

    Quote Originally Posted by charliec View Post
    thanks Haluk. that works. Also thanks for all your help JLGWhiz. Very helpful.
    You're welcome, happy to assist.
    Regards, JLG

  18. #18
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MO Prof Plus 2016
    Posts
    6,907

    Re: end of data in a column

    You're welcome. Thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 09-28-2015, 07:57 PM
  2. [SOLVED] Column A Data compared to Column B and Corresponding Data in Column A shown in Column C???
    By EverCheck in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-24-2015, 11:45 AM
  3. [SOLVED] Convert column B as multiple column titles and move data in column C into new columns?
    By princesscathryn in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 06-30-2014, 07:31 PM
  4. Replies: 3
    Last Post: 12-12-2013, 04:00 PM
  5. Compare data from a column in a crystal report to a column in excel and post data
    By dkrzysik in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-31-2012, 02:02 PM
  6. Macro to copy and paste special values for column data and filter column data
    By ascottbag in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-10-2012, 02:15 PM
  7. Replies: 3
    Last Post: 02-08-2010, 06:18 PM

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