In VB macro how do I select range from active cell

Hey guys and gals, Thanks for lookin at this problemmmmm???

When I am writing a macro using visual basic, how do I select a range
which includes 2 rows down and 8 rows across (selects the range not
just the single cell) from the currently selected/active cell  then
copy it: All this starts from the cell the user starts from.
Example......

PuzzeledProgramer


-- 
spydor
------------------------------------------------------------------------
spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438
View this thread: http://www.excelforum.com/showthread.php?threadid=496798

0
12/29/2005 9:24:25 PM
excel 39879 articles. 2 followers. Follow

5 Replies
666 Views

Similar Articles

[PageSpeed] 53

    ActiveCell.Range("A1:H2").Select

Andrea Jones
www.allaboutoffice.co.uk
www.allaboutclait.com

"spydor" wrote:

> 
> Hey guys and gals, Thanks for lookin at this problemmmmm???
> 
> When I am writing a macro using visual basic, how do I select a range
> which includes 2 rows down and 8 rows across (selects the range not
> just the single cell) from the currently selected/active cell  then
> copy it: All this starts from the cell the user starts from.
> Example......
> 
> PuzzeledProgramer
> 
> 
> -- 
> spydor
> ------------------------------------------------------------------------
> spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438
> View this thread: http://www.excelforum.com/showthread.php?threadid=496798
> 
> 
0
12/29/2005 9:33:04 PM
Activecell.Resize(2,8).Select

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"spydor" <spydor.20tlvn_1135891501.0429@excelforum-nospam.com> wrote in
message news:spydor.20tlvn_1135891501.0429@excelforum-nospam.com...
>
> Hey guys and gals, Thanks for lookin at this problemmmmm???
>
> When I am writing a macro using visual basic, how do I select a range
> which includes 2 rows down and 8 rows across (selects the range not
> just the single cell) from the currently selected/active cell  then
> copy it: All this starts from the cell the user starts from.
> Example......
>
> PuzzeledProgramer
>
>
> -- 
> spydor
> ------------------------------------------------------------------------
> spydor's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28438
> View this thread: http://www.excelforum.com/showthread.php?threadid=496798
>


0
bob.phillips1 (6510)
12/29/2005 9:33:28 PM
When in VB Editor check out help on "resize"



Gord Dibben  Excel MVP

On Thu, 29 Dec 2005 16:33:47 -0600, spydor
<spydor.20tp4b_1135895703.8885@excelforum-nospam.com> wrote:

>
>Bob...
>
>Sweeeeet!!  This is a nice solution to "ShouldBeSimple" thing to find
>with the Excel Help menus.
>
>Sure Appreciate the Info.........
>
>Spydor

0
gorddibb (17)
12/29/2005 9:45:17 PM
Bob...

Sweeeeet!!  This is a nice solution to "ShouldBeSimple" thing to find
with the Excel Help menus.

Sure Appreciate the Info.........

Spydor


-- 
spydor
------------------------------------------------------------------------
spydor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28438
View this thread: http://www.excelforum.com/showthread.php?threadid=496798

0
12/29/2005 10:33:47 PM
Andrea's solution is very similar, it is just perhaps not so obvious that
Range("A1:H2") refers to 2 rows and 8 columns from the actrivcell, it does
not refer to the actual range A1:H2.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"spydor" <spydor.20tp4b_1135895703.8885@excelforum-nospam.com> wrote in
message news:spydor.20tp4b_1135895703.8885@excelforum-nospam.com...
>
> Bob...
>
> Sweeeeet!!  This is a nice solution to "ShouldBeSimple" thing to find
> with the Excel Help menus.
>
> Sure Appreciate the Info.........
>
> Spydor
>
>
> -- 
> spydor
> ------------------------------------------------------------------------
> spydor's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=28438
> View this thread: http://www.excelforum.com/showthread.php?threadid=496798
>


0
bob.phillips1 (6510)
12/29/2005 11:11:43 PM
Reply:

Similar Artilces:

How do I change a word's style and not select the whole word?
In PowerPoint, I can change a word's style (to bold, italics, or underline) simply by clicking somewhere in the word and then clicking on the style I want. However, in Word, I have to highlight the entire word in order to change the style. Is there a way to click in the word and change the style without having to select the whole word first? Tools > Options > Edit (Word 2007 - Word Options > Advanced > Editing Options) - check 'When selecting automatically select entire word'. -- <>>< ><<> ><<> <>>< >&l...

IF Formulas with Ranges
Hi! I'm trying to create an IF formula where the workbook will recognize if there is data added to a column D range (Sessions) and then apply a formula to the last cell of column F range (Aimline). Hence, if Sessions (column D) which now has the numbers 1-27 in it gets 28 entered below, then the cell in the same row but column F will take the number 28 and apply the formula above it =$F$8+($A$12*D35). Can anyone help? cabybake =IF(D27="","",$F$8+($A$12*D27) made an assumption that D35 should relate to D28 -- HTH Bob Phillips (remove nothere from email address...

VB distmon?
Hi I have recently installed Office 2003. A couple of wired things happen in Excel: > Everytime I close the program I get a MS VB error that reads "Compile error in hidden module: Distmon" Every time open the program I get the same error, but the macro it is calling is "autoexecnew" > Since my new installation, I type in an simple function like TRIM and it tells me cannot find project library. any ideas? Thnaks! Alan Alan http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 OFF: "Compile Error in Hidden Module" Error Message When You Sta...

nav shortcut: clicking border of highlighted cell?
I wonder if any of you can help? I found the attached on an archive search of this ng via Google. I've noticed the feature mostly by triggering it inadvertantly. Does anyone know what this feature is called and/or how to turn it off? Thanks in advance for any help. Joel > > *Easiest and quickest* way to move to the end of data is simply: > > > > Select a cell in the data range- > > Move cursor to cell border until it becomes an arrow- > > > > NOW, double click on cell border in the direction that you wish to travel. > > > > Top border...

E-mail macro
Hi all, I have a workbook that I send out daily to a contact list withi outlook. What is the best way of doing this with a macro to work off a button o the worksheet. Thanks in advance -- greg746 ----------------------------------------------------------------------- greg7468's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=903 View this thread: http://www.excelforum.com/showthread.php?threadid=46988 Here's how to send a workbook from Excel as an attachment: Create a macro with the code below and then create a button that calls that macro. Keep in min...

Custom Header = Cell Contents...
Using Excel 97 Is it possible to have a custom header (Page Set up>Header Footer> Custom Header) reference the contents of a cell? I've tried" =Sheet1!A1" etc but this just prints =Sheet1!A1" Any ideas??? Right-click the Excel LOGO near the file menu, select View Code, enter this: Private Sub Workbook_BeforePrint(Cancel As Boolean) Activesheet.Pagesetup.LeftHeader = Sheets("Sheet1").Range("A1").Value End Sub "Wayne" <anonymous@discussions.microsoft.com> wrote in message news:f7b801c43dfb$70323240$a501280a@phx.gbl......

Macro #30
I want to load a file from Qbooks and then run change the columns with numbers to a format that doesn't use commas. What would be the easiest way? Thanks. Can QuickBooks (I assume that's what you mean) export to a text file that Excel can read? On Sat, 30 Oct 2004 13:30:58 -0500, "rob p" <nospam*wlb@stans.net> wrote: >I want to load a file from Qbooks and then run change the columns with >numbers to a format that doesn't use commas. What would be the easiest way? >Thanks. > ...

Excel VB
I have two cells in a workbook that are dates. The cell Start_Date has a valid date in it. The second cell Test_Date is either blank or has a valid date in it. The IsDate test on both cells works fine, however, on a blank cell the Month function extracts "12". Anyone know why? I had to put the month function within a test for IsDate to make it work. Seems like Month function on a blank cell should return something other than a 12. Dave Test code: If IsDate(Range("Start_Date")) Then 'Cell has 11/01/09 in it. MsgBox ("start date a d...

Swing Chart with dynamic Range
I would like to create a swing chart (stock chart) with a dynamic range that grows as more data is added. Any ideas? Thank You Joe Hi, For information on the dynamic ranges have a read of Jon Peltier's page http://peltiertech.com/Excel/Charts/Dynamics.html Depending on which stock chart you want you will need 3 to 5 named ranges. Cheers Andy Joseph Sayah wrote: > I would like to create a swing chart (stock chart) with a dynamic range that > grows as more data is added. > > Any ideas? > > Thank You > Joe -- Andy Pope, Microsoft MVP - Excel http://www.andyp...

Copying cell data automatically?
If you want something from cell (A) to appear in cell (B) and be dependent on the first cell, i.e, when cell A text changes so does cell B, how do I do that? It depends You can use an IF statement =IF(A1=1,"Something","SomethingElse") Or if there are many different inputs you can create a table somewhere A One B Two C Three And use VLOOKUP HTH PC "Acurran" <Acurran@discussions.microsoft.com> wrote in message news:50C72329-771C-45E4-8945-E5EB71C5BDCB@microsoft.com... > If you want something from cell (A) to appear in cell (B) and ...

Landscape macro
I need to be able to have my macro change from landscape to portrait and back when I use my print macro. Any ideas how to do this? Thanks One way: You could put an InputBox in your print macro. When it pops up you would enter P or L or whatever you want to use. Then add code to the macro to change the setup accordingly. HTH Otto "uj" <anonymous@discussions.microsoft.com> wrote in message news:349701c3e1f1$7e3e35c0$a501280a@phx.gbl... > I need to be able to have my macro change from landscape > to portrait and back when I use my print macro. Any > ideas...

Cannot Add any window to VB
Hello: I'm using a VM with GP 10.0 SP3 installed. In order to have the same as our production databases i copy the GP folder, entirely, which i think it was a mistake now I'm getting a "This cannot be added to Visual Basic", whenever i try to add a window, any window, to VB. Also it goes without saying that there's a lot of load errors starting GP. For the record the copy/paste thing worked, sort of, and before you start thinking that i was insane to have done this, there's a reason behind it, There's been a lot of tweaks to our production server, by our p...

Two Calculations in one cell?
Is it possible to have 2 separate calculations in one cell? I have a summary Table on one Sheet which gets its values from two other sheets. D E F G H I 2000/1 2001/2 2002/3 2003/4 2004/5 20 Bonus: Area 15 11 10 6 10(2) 21 Bonus: National 28 29 63 22 39(5) 22 Voucher : Area 22 52 84 54 52(7) 23 Voucher : National 36 7 25 21 17(3) 24 Team ...

Macro: make list of all files in all folder with sub folders
Hey guys You have kindly helped me before, and I hope for a little bit more My macro (below) makes a filename-list of all FILES in one certain (SUB)FOLDER, but I need to redesign it to make a list of ALL FILES in FOLDER + ALL FILES in every SUBFOLDER (on every sublevel) in this Folder-structure. I think you guys know the trick - will you share it with me? Best regards Snoopy My macro goes like this: (How can I modify it?) Sub ListAllFilesInFolderWithDir() ' Delete existing list in sheet FILES/column B to prepare for new input On Error Resume Next Sheets("...

How to write a macro to hide an entire row
Hello guys, I would like to write a macro that would hide an entire row if the cells value in that column is equal to 0. Someting like this: A B C 1 20000 2 0 - if c2 = 0 then hide entire row 3 20000 Thank you for your help. José Hi, Try this Sub Hide_Me() Set sht = Sheets("Sheet1") ' Change to suit lastrow = sht.Cells(Cells.Rows.Count, "C").End(xlUp).Row For x = lastrow To 1 Step -1 If sht.Cells(x, 3).Value <> "" _ And sht.Cells(x, 3).Value = 0 Then sh...

Trying to save spreadsheet with macros???????
A co-worker sent me a form in Excel 2007 that I need to save, fill out, and return. I tried to save is with macros enabled but I keep getting a window with the message "This workbook contains Excel 4.0 macros or Excel 5.0 modules. If you would like to password protect or restrict permission to this document, you need to remove these macros". I'm not worried about restricting the workbook. How do I get past this issue? ...

Selectively Update Field in Table
I need to selectively update a field in my master table with new data. By selective I mean only records that meet a particular criteria need to be updated. My new data is in a different table, and they are related by repair order. This is the SQL code that Access generates: UPDATE RepairData LEFT JOIN DallasData ON RepairData.[Repair Order] = DallasData.[Repair Order] SET RepairData.[Material Out] = [DallasData].[Material Out] WHERE (((RepairData.Plant)="1103")); I know there are approx 75k records that need to be updated, but when I go to run this query Access ...

Formula and macro
I'm creating a macro that will import a text file, then insert a column and then insert a formula in that new column. I only want the formul inserted in only the rows that have data in column a,b,c. Problem i that the import file will vary in sizes. One time it migh have 10, th another time it might have 100. I can't just do regular copy, becaus I'm also inserting subtotals (with summary at bottom) and the summarr appears below the last copied cell. SO if I was to copy the formul down to row 100, but there is only 10 rows of data in my import file the summary would appear 90 ro...

sending e-mails with macros
I want to send e-mails automatically with Excel's (or Outlook's) macro, but Outlook asks me each time before sending the e-mail whether I want to allow the program to send the e-mail on my behalf. How can I skip this security procedure, so that no human interaction is required? ...

format cells #2
I'm trying to format a cell in a spreadsheet that was sent to my office. When I click on "format cells...", nothing happens. I've never had this problem before. What gives? It sounds like the worksheet may be protected. I'm guessing "Cells" under "Format" is dimmed, meaning it is unavailable. Try: Tools > Protection If the choices include "Unprotect Sheet...", then it was protected, in which case, clicking on "Unprotect Sheet..." will disable the protection and you should be able to format to your heart's content. Regards...

vb from VC++
Hi, is it possible, and if so, to call a Vb macro from VC++. Or manipulate spreadsheets (ie draw graphs from the data within the workbooks) usinbg MFC functions cheers Anthony Presumably you are referring to MS Excel spreadsheets and workbooks. The easiest method would be to import the appropriate type library (.tlb) provided with Excel, having the class wizard generate the classes, which you then use to access the Excel Automation model (that is the same model that Excel VBA uses). Some investigation may be needed to determine which tlb to use, and to make sure you have a basic understand...

Going between documents using macros
I'm using Word 2004. I have two documents open. Is there a way, using macros, to go from Document 1 to Document 2, where I'll go to a certain line and copy it, then go back to Document 1 and save it using the text I have just copied. I have the macro down for going to the line, copying, and saving. What I need is the part where I go from doc to doc without regard to the document name. So I'll be in "Active Document" first, then I need to go to "?" (other Document) then back to "Previous Document." So I just need the navigation part. Here...

Subscript out of range
i keep getting an subscript out of range error here [lrowcountref is active rows, start in e2 where the data starts, hence 2 to rowcount] ReDim arrEmailAdd(2 To lRowCountRef, 2 To lRowCountRef) Range("E2").Select For z = 2 To lRowCountRef ********here is the error********* For x = 2 To lRowCountRef arrEmailAdd(z) = ActiveCell.Value arrEmailAdd(x) = ActiveCell.Offset(0, -3).Value ActiveCell.Offset(1, 0).Select Next Next Your problem is that you have defined a 2D array, but you are trying to load it as if it were a single dimension. I am struggling to see what you...

VB in Excel
In Excel I go into Visual Basic Editor, then Visual Basic Help, but when I type 'Passwords' for example it will bring up a list of related items, but some of them you can't select. Does this mean the installation CD is corrupt and hasn't installed all the components? I have reinstalled and repaired the installtion but to no avail. Any ideas anyone? Help! Thanks ...

Returning an Array from a called function within a macro
How do I get a function to return an array. [I know that it should be doable since the various matrix functions return an array.] To be more precise I need to do this within a macro. I know how to do it at the spreadsheet level. I am running Excel 2004 on a MAC [OSX] Thanks Brujolito One way: Public Function foo() As Variant foo = Array(1,2,3,4,5) End Function You can then array-enter the function into, say A1:E1, =foo() to get the values 1->5 into those cells, or use =SUM(foo()) ===> 15 You can obviously get much more complicated... In article &...