+ Reply to Thread
Results 1 to 2 of 2

Non-adjacent area selection

  1. #1
    Petr
    Guest

    Non-adjacent area selection

    Hallo,
    I want to select all the cells within non-adjacent area, e.g. I want
    to select the range B2:D4 when I have filled cells B2, B4 and D2,
    assuming cell D4 as the last cell.


    Here is my sample code:

    Sub select_non_adjacent()
    Dim last_col, col_num, res As Integer



    Set rng1 = Cells.Find(What:="*", After:=Range("A1"),
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    Set rng2 = Cells.Find(What:="*", After:=Range("A1"),
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)

    'LastRow = Application.Max(rng1.Row, rng2.Row)
    lastcol = Application.Max(rng1.Column, rng2.Column)

    Do
    Set rngResponse = Application.InputBox("Select initial cell",
    "First cell selection", Selection.Address, , , , , 8)
    If Err.Number <> 0 Then Err.Clear: End
    Application.Goto rngResponse
    intConfirm = MsgBox("Is active cell corect?" & vbCr & vbCr &
    rngResponse.Address(RowAbsolute:=False, ColumnAbsolute:=False,
    External:=True), vbQuestion + vbYesNo, "Confirmation")
    If intConfirm = vbYes Then
    ActiveCell.Select
    col_num = ActiveCell.Column


    Intersect(Range(ActiveCell, _
    Cells(Rows.Count, ActiveCell.Column)), _
    ActiveCell.Parent.UsedRange).Select
    End If
    Exit Do

    res = (lastcol - col_num + 1)

    Selection.Resize(, res).Select

    Loop


    End Sub


    I have got problem with res variable which always returns zero, i.e.
    there is no column selection to the right. I checked via Add watch the
    variables lastcol and col_num but they are correct.

    Thanks a lot in advance for any suggestions.
    Petr Duzbaba

  2. #2
    Tom Ogilvy
    Guest

    Re: Non-adjacent area selection

    In your logic, you confirm that the cell picked by the user is correct and
    then regardless of the answer exit the loop. You never get to the code
    that sets the value for res - so it would always be zero. You never loop.

    I can't tell waht you are trying to do.

    Are you trying to select from a cell designated by the user down to the last
    used row and column?

    What are you trying to achieve using the Loop?

    --
    Regards,
    Tom Ogilvy


    "Petr" <[email protected]> wrote in message
    news:[email protected]...
    > Hallo,
    > I want to select all the cells within non-adjacent area, e.g. I want
    > to select the range B2:D4 when I have filled cells B2, B4 and D2,
    > assuming cell D4 as the last cell.
    >
    >
    > Here is my sample code:
    >
    > Sub select_non_adjacent()
    > Dim last_col, col_num, res As Integer
    >
    >
    >
    > Set rng1 = Cells.Find(What:="*", After:=Range("A1"),
    > SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    > Set rng2 = Cells.Find(What:="*", After:=Range("A1"),
    > SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    >
    > 'LastRow = Application.Max(rng1.Row, rng2.Row)
    > lastcol = Application.Max(rng1.Column, rng2.Column)
    >
    > Do
    > Set rngResponse = Application.InputBox("Select initial cell",
    > "First cell selection", Selection.Address, , , , , 8)
    > If Err.Number <> 0 Then Err.Clear: End
    > Application.Goto rngResponse
    > intConfirm = MsgBox("Is active cell corect?" & vbCr & vbCr &
    > rngResponse.Address(RowAbsolute:=False, ColumnAbsolute:=False,
    > External:=True), vbQuestion + vbYesNo, "Confirmation")
    > If intConfirm = vbYes Then
    > ActiveCell.Select
    > col_num = ActiveCell.Column
    >
    >
    > Intersect(Range(ActiveCell, _
    > Cells(Rows.Count, ActiveCell.Column)), _
    > ActiveCell.Parent.UsedRange).Select
    > End If
    > Exit Do
    >
    > res = (lastcol - col_num + 1)
    >
    > Selection.Resize(, res).Select
    >
    > Loop
    >
    >
    > End Sub
    >
    >
    > I have got problem with res variable which always returns zero, i.e.
    > there is no column selection to the right. I checked via Add watch the
    > variables lastcol and col_num but they are correct.
    >
    > Thanks a lot in advance for any suggestions.
    > Petr Duzbaba




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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