Non-adjacent area selection

[Petr]

Hallo,
I want to select all the cells within non-adjacent area, e.g. I want
to select the range B24 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

 

[Tom Ogilvy]

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?