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