Results 1 to 8 of 8

VBA : Select end of data range

This is a discussion on VBA : Select end of data range within the Excel Questions forums, part of the Question Forums category; Hi, When pressing Ctrl + END you are taken to the end of the current data range. I have a ...

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Posts
    953

    Question VBA : Select end of data range

    Hi,

    When pressing Ctrl + END you are taken to the end of the current data range.

    I have a spreadsheet with a macro that removes all blanks, reducing 70 rows down to 30 rows. When i press Ctrl+END it still takes me to row 70 although all that data has now been deleted. How can i set the end of data range...(want to apply a formula in column A and have it copy down to the end of range which in practice is row 30, but in reality started as being row 70)

    Tany thanks

    T

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: VBA : Select end of data range

    Hi

    You need to save the workbook first - this should reset where Excel thinks the last cell is located.
    Richard Schollar

    Using xl2013

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Posts
    953

    Default Re: VBA : Select end of data range

    Ideally would but cant as it is part of a larger macro...

    Import file and paste
    Strip blank rows
    Paste formula from A1 to bottom of range
    Colour cells

    Maybe could set up a variable that contains the row number of the last cell containing data then have formula copy from range("A1, EndOfRangeValue)

    ??

    Regards

    T

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: VBA : Select end of data range

    Yes there are other ways to identify the end of the data: for example, assuming you know that in the last row of data the B column cell would definitely contain some data (and not be blank) you can determine what this last row is with:

    Code:
    Dim lngLastRow As Long
     
    lngLastRow = Cells(Rows.Count,"B").End(xlUp).Row
    Then you can use this to determine how far down you want your formulas copied:

    Code:
    Range("A1").Copy Destination:=Range("A2:A" & lngLastRow)
    Make sense?
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular
    Join Date
    Jun 2009
    Posts
    953

    Default Re: VBA : Select end of data range

    Fantastic...

    This will save me alot of hassle

    Still quite rusty on the VBA coming from pure VB which haven't used for 4 years!

    Many thanks

    T

  6. #6
    Board Regular
    Join Date
    Jun 2009
    Posts
    953

    Question Re: VBA : Select end of data range

    Quick question...

    Only works when i specifically state the sheet it is in :

    lngLastRow = Sheets("PITA Report").Cells(Rows.Count, "A").End(xlUp).Row

    Any thoughts?

    Although I have renamed the Sheet to PITA Report...I would like to refer to it as its original document name (is that what you call it) of Sheet2!

    Seen this done but cant remember how...would make the sheet more robust so that if name changes occur, it is still referred to as Sheet2 object name

    ?

    T

  7. #7
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,677

    Default Re: VBA : Select end of data range

    Hi

    I believe you mean the sheet's code name which you can see if you go to the VBE and click on the sheet name in the top left Project Window. The Properties window should open up in the bottom left - the name at the top is the codename of the sheet (alternatively, with the sheet active, type the following in to the Immediate Window in the VBE and hit Enter:

    ?Activesheet.CodeName

    If you use that, then you are correct that subsequent renamings shouldn't cause problems (as long as your workbook is active when the code is run).
    Richard Schollar

    Using xl2013

  8. #8
    Board Regular
    Join Date
    Jun 2009
    Posts
    953

    Default Re: VBA : Select end of data range

    Can you please provide this as a practical example in code as doesnt seem to work e.g.

    referring to cell A1 in sheet 5 of workbook MainSheet (variable containing name of current workbook)

    Regards

    T

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com