Help: I want to force selection of a group of cells.

[Dab]

I'm trying to find a way to force a range of cells to be selected when any
cell within the range is selected. This so that when I cut and past a group
of cells, I ensure that I'm actually cutting and pasting (or inserting) all
cells in the group, including those that might appear off screen.

Anyone know if there is a way to do this?

Tanks for any advice.

Dab

 

[Dave Peterson]

If the cells are nicely laid out--no empty rows/columns in your big range.

Manually, click on a cell in the big range.
Hit Edit|goto|special|click on current region
(F5 or ctrl-g pops up the edit|goto dialog)

Or hit ctrl-* (shift 8) to for the shortcut key.
If you do it a lot, there's even an icon you can add to your favorite toolbar.

Tools|Customize|Commands Tab|Edit category|at the bottom of the Commands.

If you get the correct range with ctrl-*, you can do the same in your macro:

activecell.currentregion.copy _
destination:=worksheets("sheet2").range("b33")

 

[Dab]

Thanks Dave, but not really what I wanted.

I'm building a table that will have rows, moved, copied, inserted, etc..
Not all of the cells in the row will contain data, and what I worried about
is that someone may copy only a few cells in a row and then insert those few
cells in another row, displacing the data in other columns in all of the
rows.

Ideally, what I'd like is that if I mousedown in any cell, the entire row of
cells in that row of the able is automatically selected. That way, if i
cut, past, drag, whatever, it will happen to all of the cells in that row of
the table.

I have felling that it cant be done, but thanks for your try.

 

[Dave Peterson]

Maybe you could pick a row that always has data and use it:

dim LastRow as long
dim LastCol as long
with worksheets("sheet1")
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1,.columns.count).end(xltoleft).column

.range("a1",.cells(lastrow,lastcol)).copy _
destination:=somewhereelse....
end with


It kind of sounds like you want the user to select the range (might be the
simplest if it moves around a lot???):

dim myRng as range
on error resume next
set myrng = application.inputbox(prompt:="select a range", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
exit sub '???
end if

myrng.copy _
destination:=thatotherplace....