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