Articles

how to copy multiple non-adj cells

March 4, 2009 at 06:32:11
Specs: Windows XP

how can i select multiple non adjacent cells that are the same number of rows apart and paste them adjacent to each other in MS excel?
I cant use Ctrl because i need to scroll down or use page down.
Any ideas?

See More: how to copy multiple non-adj cells

Report •


#1
March 4, 2009 at 09:47:43

You can use Ctrl still even if you have to scroll down. Just click and drag, continue dragging down off the page and the selecting should still follow.

also, you can hold ctrl and just select the other row. for example:
you have data in A and C. left click A (which would select row A) then hold ctrl and left click C. You now have both rows selected.

Don't forget you can also use Shift+left click to select a large number of cells. just left click which cell you want to start then scroll down to the last cell you want selected then shift+left click to select all the cell in between.

You can also use Ctrl and shift to deselect already selected cells.

Hope this helps.


Report •

#2
March 4, 2009 at 09:58:12

re: You can also use Ctrl and shift to deselect already selected cells.

Could you explain this? I don't have Excel 2007, but in every other version of Excel I've used, you can not deselect an individual cell that is part of a group of cells selected using the Ctrl key.

In other words, let's say I select A1 an then hold down the Ctrl key and select A4, A7 and A8. I don't believe that there is any way to deselect A7 without deselecting the entire group.

Do you know of a way to do this?


Report •

#3
March 4, 2009 at 11:23:29

I am using Excel 2007 (on this machine) and DerbyDad03 you are correct in your findings in this matter (although I thought otherwise in earlier versions. I swear I have deselected cells before but definately not within Excel 2007).

Bryan


Report •

Related Solutions

#4
March 4, 2009 at 13:02:22

I've never been able to do it in any version and it basically sucks.

There's not much worse that selecting a large number of non-adjacent cells and then accidentally selecting one that you don't want.

All you can do is start all over again.

Oh wait...I've got a worse one. I have a database application that displays a list of over 26,000 items upon opening.

F8 opens a search window. After you enter the code or name for the item you want and hit enter, it highlights your choice in the main list. You then hit F8 again to search for, and highlight, the next item. Sometimes we need to highlight 20 - 30 items, one at a time via F8.

Very close to F8 is F9. F9 selects every item in the 26,000 line database.

So you're tooling along - F8, code, Enter, F8, code, Enter, F8, code, Enter - maybe 20, 30 times. Then you accidentally hit F9 and every single item gets highlighted.

There is no Ctrl-z. You have to start all over again. F8, code, Enter, F8, code, Enter, F8, code, Enter

We've begun to recognize the groans from the other offices when someone else makes the same mistake.


Report •

#5
March 4, 2009 at 14:01:21

I forgot to mention I have a macro set up on my personal excel template to allow deselection of cells. Here is the macro pulled up from my personal template:

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If

End Sub

I use excel 2000 and this macro works for me to deselect cells.

Hope this helps.


Report •

#6
March 4, 2009 at 17:15:00

Also DD. If you continually have this problem with pressing the F9 there is a way to rebind that key. Although I don't know how to rebind it in excel. But here is a program that you can remap your keys.

http://webpages.charter.net/krumsick/

A little warning though this will remap keys for everything. So it all depends, do you use F9 a lot? If not then why not get rid of it and never have the problem again.

Hope this helps.


Report •

#7
March 4, 2009 at 18:35:32

re: Your unselect code.

I like the code, but I'm not sure how it relates to this statement:

"You can also use Ctrl and shift to deselect already selected cells."

Could you elaborate?

re: Remapping F9.

Locked down, corporate image with no Admin rights. We can't even install the monthly database upgrades that we pay for out of own pocket. We have to send the CD's to our IT department who copies them to a server and remotes in to our machines to install the updates.

Downloading and installing an exe just can't be done.

Thanks anyway!


Report •

#8
March 5, 2009 at 02:51:50

Using ctrl does work but its very time consuming. I need to select
100s of cells that are all exactly 78rows apart from each other. Is
there a simpler/faster way of doing this?
How would i go about writing a code to do this for me?
I'v never written one before.

Report •

#9
March 5, 2009 at 06:54:25

Before we code offer any code that would work for your specific situation, we would need some detail.

As an example, the code below will select A1, A79, A157 and A235.

Sub Select78()
'Set First Row Number
 MyRow = 1
  For NxtRow = 1 To 4
'Build the Range to be selected - A1, A79, etc.
   BuildMyRange = BuildMyRange & "A" & MyRow & ","
'Add 78 to the Row Number
    MyRow = MyRow + 78
  Next
'Strip off last comma
   MyRange = Left(BuildMyRange, Len(BuildMyRange) - 1)
'Select the range
   Range(MyRange).Select
End Sub


Report •

#10
March 5, 2009 at 07:19:14

yes that seems like it would do the trick, where in excel would I write that code. I could then change the cell numbers to suit.
sorry for basic questions but I'm new to excel.

Report •

#11
March 5, 2009 at 07:27:13

One place to put the code would be a VBA module in your workbook so it could run against the active sheet.

Alt-F11, Insert...Module

or you could Right Click a sheet tab and select View Code to open a window for code that will only run against that sheet.

Finally, if you create a personal.xls file in the xlStart folder and store your code there, it would be available for any and all workbooks.


Report •

#12
March 5, 2009 at 07:47:55

Thanks,

I tried to run that code in vba but an error window came up saying;

"run time error 1004

Method 'Range' of object '_Global' failed"

it highlights the last line of the code when I press debug??


Report •

#13
March 5, 2009 at 09:41:46

Sorry to highjack your thread roryb but I gave you a little bit of confusing information when I forgot to include (remember on my part) that I have customized my excel to fit my needs by having code and remapping my keys.

DD thanks for catching that, I hate to give incorrect information, but I have my keys set up differently for when excel runs. For most other programs you can deselect text with the shift or Ctrl keys, so I have those keys bound to the macro I posted. So for me when I click Ctrl or Shift to deselect cells I am actually running the macro to deselect, so essentially I have my keys bound to the macro but they can still perform their original intended functions as well.

Hope this helps clarify things.

To answer this question:
Using ctrl does work but its very time consuming. I need to select 100s of cells that are all exactly 78rows apart from each other. Is there a simpler/faster way of doing this?

Is it possible... instead of selecting the cells, that you can select the entire colum/row? You can do this by clicking the row A then holding down ctrl and select row B. I don't know how packed your workbook is but this is a simple solution that I hope helps.

And remember that if you select entire rows/columns and they are not... too packed full of data you can always copy the entire row/columns, paste, then just delete the data you don't want to be included.


Report •


Ask Question