Define Range, Given Cell Address To End Row

  • I want to define the range from a given cell and all data below within the same column
    strDataStart is the named cell in the worksheet that want to start from


    Below is what I tried and failed.



    Many thanks

  • Re: Define Data Range, Given Cell Address To End Row


    you haven't defined "strDataStart"


    try...

    Code
    Dim strDataStartAs Range 
         
         ' Define first cell
        strDataStart= activecell
  • Re: Define Data Range, Given Cell Address To End Row


    Quote from gmccreedy

    you haven't defined "strDataStart


    strDataStart is defined as string and it is the input of the function

  • Re: Define Data Range, Given Cell Address To End Row


    apologies, i didn't look at the script close enough to realize it was a function. What are you trying to do...post the range address as the answer? I am not sure if you can do this. I have never seen a dynamic range used in a function.
    perhaps the "higher ups" have your solution.


    sorry again.

  • Re: Define Data Range, Given Cell Address To End Row


    Try this,


    Remove the passed variable strDataStart from out of the quotes. The will cause the Range to be a named on rather than using the contents of the variable.
    You need to use the SET keyword with objects.
    I also rename the function to avoid confusion with range variables.


    [vba]Function GetDataCol(strDataStart As String) As Range
    '
    ' To define data range start from cell strDataStart and the rest of column
    '
    ' Written 22/11/2006 by XXX
    '
    Dim rngDataEnd As Range

    ' Define Last cell
    Set rngDataEnd = Range(strDataStart).End(xlDown)

    ' Define Data Range
    Set GetDataCol = Range(Range(strDataStart), rngDataEnd)

    End Function


    Sub test()


    Dim rngX As Range
    Dim strStartCellAddress As String

    strStartCellAddress = "B3"
    Set rngX = GetDataCol(strStartCellAddress)


    If Not rngX Is Nothing Then MsgBox rngX.Address

    End Sub[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!