Expand Range In Macro

Oct 16, 2007

I have a named range. The range includes a block of cells plus one extra row and one extra column. That is so I can insert a new row or a new column and still have them within the range. The last row and column are simply blank. I am trying to figure out how to select the part of the range that excludes the extra row and column, so I can then do a fill-right and fill-left. It seems like this should not be hard but I am getting all gummed up in the syntax. The last line of the following code excerpt produces a run-time error 13 Type mismatch. It compiles, however. (I have omitted some extraneous lines that have no effect on r or rf.)

Dim r As Range
Dim rf As Range
Set rf = Range("TotalFTEByStaff")
Set r = Range(rf.Cells(1, 1), rf.Cells(rf.Rows.Count - 1, rf.Columns - 1))

View 5 Replies


ADVERTISEMENT

Expand Selected Range

Nov 15, 2009

I need help please. Using a macro, I have selected the range C7:C12. Now I need to keep this selection and expand it 18 columns to the right. This would give me a selection of C7:U12.

The original range changes at various times. Some times it might be C7: C452 etc.

I know it's easy, but I can get the code to keep the selection.

View 6 Replies View Related

Expand Named Range

Jun 11, 2007

I am trying to dynamically manage a named range. As a new column is inserted to the right of the named range, the named range is then expanded/extended by one column to the right.

View 4 Replies View Related

Expand Active Cell To A Given Range

Aug 20, 2006

When i click commandbutton1:

Copy Active cell (columnB), next cell (ColumnC), next cell (ColumnI), next cell (ColumnO) all of which exist in the same row.

I will then paste the values into another sheet in the next available row which i think i have mastered. the cells will be pasted next to each other i.e. instead of column B,C,I,O they will be pasted as column A,B,C,D.

My dilema is i am not sure if you can use multiple cell offsets to perform this or if it is better to use a loop.

View 4 Replies View Related

Expand Active Cell To New Range

Dec 27, 2006

I have some working code that makes a number of changes to an excel report using VBA. Each time I execute the code I am taken to a different point in the excel report (depending on user inputs). What I now need to do is to change the cell selection from a single cell to three cells.

For example, my code may result in cell B100 being the active cell. What I now want to do is change the selection from B100 to the range B100:B103. I would have thought I could write something simple like:

Sheets("Group").Range("ActiveCell:Offset(0, 2)").Select

I have tried every variation of the above but I can't seem to make it run. Where am I going wrong?

View 2 Replies View Related

Dynamically Expand Range To Create Chart

Jan 21, 2009

See the attached example. I have data listed as shown, but need the chart to automatically update whenever a new sales figure is added to the next month. I know you have to use the OFFSET to create dynamic ranges but I can't get it to work horizontally, only if my data is laid out vertically.

View 2 Replies View Related

Expand Range To Include Non-contiguous Column?

Apr 1, 2014

How to make the below selected range, which represents column Q, also include column A?

Range(ActiveCell, ActiveCell.End(xlDown)).Select

View 4 Replies View Related

Expand Named Range Based On Cell Value

Jun 13, 2008

I have a workbook containing two worksheets - Sheet 1 being my "data entry sheet" and Sheet 2 is a sort of summary report containing multiple named ranges (all grouped according to similar values in a cell i.e. all cells containing 1-1 is named GRP1, all cells containig 1-2 is named GRP2). Basically, my workbook looks like this:

Sheet 1
A B C D
1 Lea Female 1-1 Lea Wells
2 Leo Male 1-2 Leo Anderson

Sheet 2
A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2

Hence, when I enter the data as shown in Sheet 1, Sheet 2 should look like this:

A B C D
1 Anne Thomas Female 1-1
2 John Doe Male 1-1
3 Lea Wells Female 1-1

4 Jimmy Beads Male 1-2
5 Mary Fox Female 1-2
6 Leo Anderson Male 1-2

View 4 Replies View Related

Pivot Table - Automatically Expand Range Selection Of Data

Oct 12, 2002

Possible to get a pivot table to automatically expand the range selection of data as it grows. I have a worksheet that grows daily and I am running a pivot table from this. If I refresh the pivot table it doesn't pick up new data unless i change the range or i have a huge range selected in the first place. Problem is if i select a huge range then the grouping options i am using won't work correctly.

View 5 Replies View Related

Excel 2010 :: Expand Text Number Range Into Separate Cells

Feb 20, 2014

Using Excel 2010.

I have data in excel which looks like this:

Column 1 has 1200-1209,1300-1350,1523-1563
Column 2 has 1400-1409,1600-1650,1823-1863

I would like to take the range of e.g. 1200-1209 and have excel put 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 into separate adjacent cells for me. And be able to do this for each column/cell of data I have like this.

Column 1 1200
Column 2 1201
Column 3 1202

Like that only. Is it possible?How?

View 4 Replies View Related

How To Expand Dropdown List

Feb 20, 2013

I have a data validation list that has approx 40 items.When I select the cell with the drop down list - only 10 or so items appear . I then have to use the scroll bar on the side to look further in the list. Is there a way that more than 10 or so ( possibly all ) items appear in the drop down box when the cell is selected - or is there a way that one can scroll down using the mouse wheel. ( I have to use the scroll bar at the moment ).

View 2 Replies View Related

Expand, Copy & Paste

Jan 22, 2009

If I select lets say cell "A46", expand the selected range from A46 to Q46 and paste that range to another sheet. I know how to do the copy and paste features, but I can't figure out how to expand the selected range. I will always be selecting a cell in column "A" and going to out to "Q".

View 9 Replies View Related

Expand And AutoFill Ranges

Dec 10, 2006

I have two ranges. I need to add a row at the bottom of the first range and autofill formulas in the second range using the new added data

View 2 Replies View Related

Expand Cell Grid

Mar 3, 2008

Looking to expand grid in Excel 2003,

View 4 Replies View Related

Auto Expand A Spreadsheet When Filling It In?

Nov 21, 2013

I've created a spreadsheet in which I want a blank row added when a previous row is filled in any of the boxes. I've used VBA code to insert a row but it just inserts a row in which all the cells are unmerged. I want a blank row the same as the ones we're filling so the document auto expands and there's always a blank row available to fill.

View 5 Replies View Related

Expand On Existing Sumproduct Formula

Feb 26, 2014

Still finding the SUMPRODUCT function a bit challenging to fully grasp.

This formula works great to sum the miles (Q16:Q38) the team (A16:A38) drove in a particular month (B16:B38)

=SUMPRODUCT(--ISNUMBER(MATCH(A16:A38,INDIRECT(E3),0)),--(MONTH(B16:B38)=E7),Q16:Q38)

How would I expand it to check yet another column say (S16:S38) for the term " Charge Call" ?

So I can see the miles the team drove in March for all the charge calls.

Where can I go to study and learn more about SUMPRODUCT. Seems like just what I need to build interactive type dashboard reports.

View 2 Replies View Related

Cell With Formula Won't Expand To Fit Text

Mar 31, 2009

I have a cell set up with a formula to copy the content from another cell. However, the cell won't expand to fit the text. If you delete the formula and just type in the cell, it does expand. See attachment.

View 5 Replies View Related

Expand / Compress List With Buttons

Jan 27, 2012

I am trying to make a spreadsheet for a customer and would like to include a feature where the list of parts can be expanded or compressed with buttons.

Each SO# has a set of PO#'s associated with it, and it would be nice if the viewer can choose to see the PO#'s or not see them at will (this will make a lot more sense if you see the spreadsheet example in my link.)

Ideally, there would be a button next to each "SO#" which would expand/compress accordingly. I don't want just a universal button that would expand/compress all of the SO# at once (although having this option on the side would be nice).

here's link to my spreadsheet: [URL]....

View 2 Replies View Related

How To Expand Beyond Row 1000 Without Correcting Each Cell

Mar 4, 2012

I have about 300 cells filled with different formula's like the ones written below.

=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$C$1:$C$1000=D21),('Book 1'!$W$1:$W$1000))

=SUMPRODUCT(--('Book 1'!$B$1:$B$1000="H"),--('Book 1'!$D$1:$D$1000=I21),('Book 1'!$S$1:$S$1000))

Spreadsheet works great, but I've realized that I should have expanded beyond the 1st 1000 cells (example: $1000). One book 1, my data entry will extend beyond row 1000.

Is there a way I can replace all $1000's on the spreadsheet with say $3000 without having to correct each of the 300 cells separately?

View 5 Replies View Related

Wrap Text And Expand Cell?

Apr 16, 2012

I have a sheet that copies text info (merged and centered cell)from a cell in another sheet (text alt+enter to create more line), the text wrap is on, however i have to manually expand the cell to see all of the text

View 2 Replies View Related

Expand And Contract Group On Click

Jul 7, 2013

I have an excel file where I have a grouping of cells. I don't trust some people to expand and contract the grouping via the tiny buttons on the left of the screen as some people using this program will have no prior experience with excel so it needs to be very simple and intuitive.

On clicking a specific cell I would like the grouping to expand if it is already contracted, and contract if it is already expanded. I would also like for this to happen outside of using VB because I hate those enable/disable macro options when you first open excel but I am willing to use VB if necessary.

View 1 Replies View Related

Trapping Outline Expand / Collapse

May 15, 2009

I suspect I need a class module for this...

How do I trap the event when a user chooses to expand / collapse outlines (columns in this instance) on a worksheet?

Basicallly, when attempting to expand I want to prompt the user to enter a password and then unprotect the sheet. And if collapsing to prompt again and then protect the sheet. I have this bit covered, just not sure how to trap the event.

View 9 Replies View Related

Expand Array Formula Criteria

Dec 8, 2006

Need to expand the crteria on this array?

{=SUM(IF(LYIND1=1,IF(PROFILE!$G$405:$G$435="EOM",(PROFILE!$AU$405:$AU$435),SUM(IF(LYIND1=2,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436)))))))-SUM($K$16:N16)}

I need to add

{=SUM(IF(LYIND2=1,IF(PROFILE!$G$406:$G$436="EOM",(PROFILE!$AU$406:$AU$436),SUM(IF(LYIND2=2,IF(PROFILE!$G$407:$G$437="EOM",(PROFILE!$AU$407:$AU$437)))))))-SUM($K$16:N16)}

View 3 Replies View Related

VB To Expand Alphanumeric Code Ranges Into New Column

Jan 8, 2013

I have a column containing ranges of apha-numeric codes that I wish to expand into an adjacent column as the following example:

AL 0-4
AL 0

E 4
AL 1

H 16-23
AL 2

[Code] .....

I would like the VB to distinguish and expand the different range permutations from the first column, leaving single codes intact and separating comma separated codes as shown. If it makes it easier, I manually split out the comma separated codes onto separate rows.

View 4 Replies View Related

Expand X Axis Based On Cell Values

May 15, 2014

I want to create a bar chart so that the X axis scale is based the value in the reference cells. For example, a table created for running profile as follow:

The data table # of minutes2221 pace (MPH)4.06.08.04.0

The chart I need to produce. The data has 4 records, but the chat needs to show bars of total number of minutes. Picture1.png How do I do it?

View 2 Replies View Related

Auto Expand Row When Typing Lenthy Text

Nov 13, 2008

I am trying to have a form that people can fill in and I know it would be far better to use Word for this purpose but it looks so much better in Excel format. The problem is I have merged about 10 cells in a row and copied this format down a few rows.

Now although I have set the 'Text Wrap' option the rows will not auto expand unti to accodate all the text unless I do a ALT-ENTER combo. The problem is anyone using this spreadsheet to complete the form may not know how to use this feature.

Is there anyway Excel can expand automatically or do I need some sort of Macro based in the Worksheet that will automatically adjust the row if the text is too long to fit?

View 5 Replies View Related

Formula To Expand Headings To Table Columns

Jan 25, 2014

The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.

My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.

The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).

I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.

View 3 Replies View Related

VBA Code - Expand / Collapse Grouped Columns

Feb 10, 2014

Currently, Columns EFG are grouped.

I have a userform with checkboxes. With the click of check box, i would like for it to do the following.

If checked, it will expand the group. If unchecked, it needs to stay collapsed.

Here is an example of code i currently have and is not working.

[Code] ......

View 2 Replies View Related

Formula To Automatically Expand Each Week As New Data Comes In

May 17, 2007

I believe it should be quite a simple thing...and probably has something to do with the OFFSET function...but I cannot seem to put my fingers to it.

All details are mentioned in the attached ZIP file...so I won't repeat myself here.

View 10 Replies View Related

VBA / Using Nested Loops To Expand Summarized Table?

Oct 14, 2013

As far as I can tell there needs to be a nested loop with some kind of variable inside a variable, but to be honest it's well beyond my current skill level... I have the file hosted on my Google Docs below.

[URL]

Company

Item
Quantity
Costs

Tesco
Cheese
4
50

Tesco
Milk
5
50

[code]....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved