Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Specifying a block of cells (XL97/WinNT4)

    Sorry, folks - not having a good day today!

    I'm attaching a sample from a file I'm dealing with (the same as in my post earlier today) and what I need to be able to do is to take a single cell and specify the block of cells of which it is a part, ie each block of dates on the attachment. I don't mind if each block includes the headings or not.

    As usual, any help gratefully appreciated!
    Beryl M


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

    Re: Specifying a block of cells (XL97/WinNT4)

    If you can include a blank row between blocks of data, you could use the CurrentRegion property of a cell.

    Range("X").CurrentRegion.Address would then return the address of the block of data containg cell X.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    Hi Andrew, thanks for replying - yes, I did think of CurrentRegion, but unfortunately I'm not allowed to alter the layout of the spreadsheet at all - it's not mine, it's maintained elsewhere. I did hope the blank cell before each set of dates might enable that to work, but unfortunately it didn't.

    I did think of using selections somehow - I can specify that the start cell is in any column, but without changing the row initially, so I thought maybe there was a way to go around, ie select from this point up to but not including a blank cell, then across until you hit a blank cell, then down the same, but I can't even do the last one manually!
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    By recording manual commands I came up with...

    Selection.End(xlToLeft).Select
    Selection.End(xlUp).Select
    Selection.End(xlToRight).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToLeft)).Select

    But this doesn't work in the top range of cells because of the blank entries in I3 and I4

    StuartR

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    Thanks, Stuart - that looks like it'll work!

    But I'll have to ask - there's obviously as set of keyboard combinations that I don't know here - how did you do it?! I used Ctrl-Shift-Up, Ctrl-Shift-Right, with gave me the top and right edges, but then Ctrl-Shift-Down just changed the block from being middle-to-top-to-right, to middle-to-bottom-to-right!

    Many thanks!
    Beryl M


  6. #6
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    The sequence of keyboard commands was

    <table border=1><td>Control-Left Control-Up </td><td>This got me to the top left cell</td><td>Control-Right</td><td>To the top right cell</td><td>Control-Shift Down</td><td>Select the right hand column down to a blank cell</td><td>Control-Shift-Left </td><td>Select the block to the left</td></table>

    StuartR

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    What is must be to actually be able to use a program instead of just being able to program in it! (albeit not very well!)

    Again, many thanks, Stuart!
    Beryl M


  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    Er - just one more question? Pretty please?

    How the <img src=/w3timages/censored.gif alt=censored border=0> do I then tell it that range variable "regRange" should contain the currently selected selection?!

    Sorry - my brain hurts today! <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>
    Beryl M


  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    set regRange = Selection

    StuartR

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

    Re: Specifying a block of cells (XL97/WinNT4)

    Beryl,

    Her is a function that migh give you some pointers. If column A of the cell in question contains a date, it attempts to put a range address on the relevant block of cells.

    Function MyAddress(c As Range) As String
    Dim rngStart As Range, rngBloack As Range
    If IsDate(Cells(c.Row, 1)) Then
    Set rngStart = Cells(c.Row, 1).End(xlUp)
    rngStart.Select
    Set rngBlock = Range(rngStart, rngStart.End(xlDown).Offset(-2, 8))
    MyAddress = rngBlock.Address
    Else
    MyAddress = "N/A"
    End If
    End Function


    See if you can get it to help.

    Andrew C

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    So how come I couldn't work that out, huh?! <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> Oh, well. I told you my head hurts today. Roll on the weekend ...
    Beryl M


  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    Wow! That looks like fun ...

    If I'm reading it right, it selects a block that includes the header rows (that's the -2, yes?) and is 8 columns wide ...

    Yes, I think I might well be able to get it to help!!

    Muchos gracias, Andrew, and have a nice weekend! (she says, keeping her fingers crossed she won't be back with another question in ten minutes ...)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


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

    Re: Specifying a block of cells (XL97/WinNT4)

    I have not teasted in all situations so you may need to check it out.

    Also no need for the line rngStart.Select

    Andrew

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

    Re: Specifying a block of cells (XL97/WinNT4)

    Actually the minus 2 is to emove the header from the following block. You can insert a further -2 if you want to include the header rows.

    Set rngBlock = Range(rngStart.Offset(-2, 0), rngStart.End(xlDown).Offset(-2, 8))

    Andrew

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Specifying a block of cells (XL97/WinNT4)

    Good thing I asked, then, eh?!

    Ta muchly!
    Beryl M


Page 1 of 2 12 LastLast

Posting Permissions

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