Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Coding for cell{end}{down}

    Hi there,
    As you can see from the coding below, I have named a cell "job". I now want to select that cell + the cell just underneath it. It might not be the exact row number below the cell "job", as the user can filter the data. The **** show my problem row. I have tried this - activesheet.range("job", activesheet.range("job").end(x1down).select.
    This coding didn't work.

    Range("job").Select
    **** Range("D1821").Select '**** here I want to select cell "job" x1 row down
    Selection.Copy
    Range("F18").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G18").Select
    End Sub

    Please help as you always do.

    Thanks a lot

  2. #2
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding for cell{end}{down}

    Hi Rene,

    I do not know what your end result is meant to be, but this will select the cell immediately under "job":

    Cells(Range("job").Row + 1, Range("job").Column).Select

    From your post I could not tell whether "x1" meant one row below or "x1" was a variable. If you wanted to select the cell "x1" number of rows beneath "job", then change your code to this:

    Cells(Range("job").Row + x1, Range("job").Column).Select

    HTH,

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding for cell{end}{down}

    I am not sure if this is what you want:

    Range("Job").Rows(Range("job").Rows.Count).Resize( 1, 1).Offset(1, 0).Select

    This command selects one cell immediately below the last row of the "job"-range. Maybe there are other (or shorter) ways to do it.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Coding for cell{end}{down}

    Rene,
    If you are trying to skip over non-visible cells, try the following code :

    Dim bIsHidden As Boolean
    bIsHidden = True
    Range("job").Select
    Do While bIsHidden
    ActiveCell.Offset(1, 0).Select
    bIsHidden = ActiveCell.EntireRow.Hidden
    Loop
    Selection.Copy
    Range("F18").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("G18").Select

    Hope that works for you

    Andrew

  5. #5
    zenood
    Guest

    Re: Coding for cell{end}{down}

    I might be mistaken, but I think what you want is to select the next visible cell in a filtered / non-filtered list

    I've attached a sample Excel workbook with a bit of VBA code.
    You can copy-paste the code into your own modules.

    If you want, I can explain how the code works.
    Hope this helps !
    Ciao

  6. #6
    zenood
    Guest

    Re: Coding for cell{end}{down}

    Sorry, I think the file was not attached to my earlier post !

  7. #7
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Coding for cell{end}{down}

    Hi Andrew,Michael, Hans & Zenood

    Thank you all for the info. Two of the answers I can use if I don't have hidden cells (in filters), and Andrew's and Zenood's I can use if I filter a range.

    Enjoy your day.

Posting Permissions

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