Select large cell area and others

[steve263]

Good day all, As a new member, I would like to start by asking two questions. First of all, I often need to select a large area of cells for the purpose of copying and pasting. This area changes size quite often depending on the information put in. Is there a way of selecting the area from A1 to the end of the range in one go, when you do not know the last cell number, or do I have to find the last cell number first. (say F700)

The second question is this. I am trying to create a workbook with information that when used, will not show on a third worksheet. i.e. If I put numbers 345 654 789 410 on one sheet, and three of those numbers are repeated on a second sheet, can I use a formula on a third sheet that will only bring up the fourth number? I guess there must some sort of "vlookup" formula, but I am not experienced enough to get it. I will be using over 200 different numbers eventually, but the fill in formula will sort that out.

 

[Smitty]

Welcome to the Board!

For the first, these might be a start:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> CopyVariable()
    <SPAN style="color:#007F00">'   Copy single column</SPAN>
    Range(ActiveCell, ActiveCell.End(xlDown)).Copy
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> CopyVariable2()
    <SPAN style="color:#007F00">'   Copy UsedRange</SPAN>
    ActiveSheet.UsedRange.Copy
End <SPAN style="color:#00007F">Sub</SPAN></FONT>

For the second, will the numbers be in individual cells, or will "345 6545 789 410" all be in one cell?

Can you post an example? Colo's HTML Maker will let you do that, you'll find the link at the bottom of the page. Just read the HTML Maker FAQ first.

Hope that helps,

Smitty

 

[Zack Barresse]

Also check out this link for the Last Cell ...

http://www.xldynamic.com/source/xld.LastValue.html

 

[steve263]

Thanks for the answer

Thank you for such a quick reply. AS for the second answer I am off this weekend, and will post the file then as soon as I get a bit of time. As for the first I am afraid I got a bit lost with that. Where did these "subs" come from? And do I have to put a = sign before the formula? Sorry to sound a bit thick, but although I have been using excel for some time now, others generally make up the formula's. This is somehing a bit new to me.

 

[Zack Barresse]

Those are not formulas, they are VBA (Visual Basic for Applications) routines. They are code and go in the VBE (Visual Basic Editor). To install the code ..

With the workbook open/active you want:
Right click the workbook icon directly left of File menu
Select View Code
Copy/Paste the code in the right pane
Alt + Q will return you to Excel
SAVE FIRST

 

[steve263]

VBE

Thanks for the answer Zack, but there is one more thing. VBE is something that I have never used, and the office does not use it either. I have the code installed in a copy version of one of my files, just to mess with. What the heck do I do with it now I have it? You must think I am really thick, but although I have played with a macro or two, I have not really bothered to much with it, and VBE I have barely heard of.

 

[Nimrod]

Is there a way of selecting the area from A1 to the end of the range in one go,

1. left click on cell A1
2. Hold Shift key down while pressing "End" key and then "Down Arrow" key.

 

[steve263]

Shift end and down

Thanks for that one, it works a treat. I would still like to know about VBE though, as it might come in useful another day.

 

[Peter_SSs]

steve263

A bit more on selecting a (large) range.

Put this information on a sheet, then see below for some things to try.

Mr Excel.xls
	A	B	C	D
1	1	2	8
2	2	3	6
3	3	5
4		6
5		8
6
7		2
8		3
9
Range Selection

Now try these:
1. Select A1. Hold SHIFT and double click (DC) the bottom border of A1.
2. Select B5. Hold SHIFT and DC the top border of B5.
3. Select A1:C1. Hold SHIFT and DC the bottom border of A1.
4. Select A1:C1. Hold SHIFT and DC the botom border of B1.
5. Select A1:A3. Hold SHIFT and DC the right border of A3.
Get the idea? Also:
6. Select A1. Press CTRL+SHIFT+END
The last one should select A1:C8. While that is still selected:
7. Press CTRL+. repeatedly. This is very handy for moving from top to bottom (or vice versa) of a long column or large range.
8. If you have a long column (or row) and you don't want to select the whole thing but want to get to the bottom (or right/top/left) try this. Select B1. DC the bottom border of B1 (no SHIFT this time).
Experiment.

 

[Zack Barresse]

Re: Shift end and down

I would still like to know about VBE though, as it might come in useful another day.

A good start can be found HERE.

HTH