Resizing Non-contiguous Range

Apr 8, 2008

Is it possible to re-size a non-contiguous range? I'm guessing "no", and a Google trawl hasn't provided me with anything.

Here's what I'm trying to do:


dim mult_rng as range
dim rng2 as range

set mult_rng = range("A1:B1,D1:E1")
set rng2 = mult_rng.resize(rowsize:=10)
That is, my original range is comprised of non-contiguous cells in the same row. When re-sizing, I'm trying to increase the number of rows, but leave the columns the same.

Like I said, my gut instinct says that this isn't possible using the 'resize' property. Can anyone think of another method to use? My constraints are that the original mult_rng isn't always the same (and isn't always non-contiguous).

View 9 Replies


ADVERTISEMENT

Resizing Range With VBA

Sep 30, 2012

I have table1 that has its original range as A1:B3. Depending on the amount of n in another column, I want table1 to be resized to A1:Bn.

I have tried with

ActiveSheet.ListObjects("Table1").Resize range("A1", "witdh")

with "width" being a variant.

View 9 Replies View Related

Resizing Range In VBA

Oct 26, 2013

I am trying to copy down a range of formulae using the resize function. Here's the code I have :-

Code:
Dim no_of_transactions As Long
no_of_transactions = Last_Row_of_data(main_workbook.Sheets("working").Range("g2"))
main_workbook.Sheets("working").Range("w2:ak2").resize(no_of_transactions,1)

However I am told by the vba editor that I need an = sign ? The function Last_Row_of_data tells me the number of rows that I have.

View 4 Replies View Related

Resizing Range VBA

Apr 18, 2006

I have a worksheet named Advisers with a range also named Advisers, the range is a list of names which is added or deleted from by use of a UserForm.

I have the following lines of -
The first populates the worksheet
and
The second is supposed to resize the range Advisers accordingly but it gives a Run-time error I've tried the 2 examples below


c.Offset(0, 0).Value = Me.Adviser_txt.Value

Worksheets("Advisers").Resize(Range("Advisers").Rows.Count, 1).Name = "Advisers"

c.Offset(0, 0).Value = Me.Adviser_txt.Value

Worksheets(Sheet2).Resize(Range("Advisers").Rows.Count, 1).Name = "Advisers"

View 9 Replies View Related

Range Name Resizing

Aug 2, 2007

= SUMIFS(E9:E1494,C9:C1494,1) This formula works ok. If I rename range E9:E1494 as "Col_1" and change the formula to =SUMIFS(Col_1,C9:C1494,1). I get #VALUE error.
The range is formatted as number.

Also ...

If I delete some cells in a range, referring formulae are modified accordingly, but if I add cells in the range the formulae do not recognize the change

View 4 Replies View Related

Grouping Contiguous List Into Non-Contiguous Groups

Sep 9, 2006

I would like to be able to count the amount of entries in column C and depending on the amount group them in either groups of 3 or 4, all names would be unique...so if there are 14 names in the list they would need to be grouped in to two groups of 4 and two groups of 3, if there were 19 then 4 groups of 4 and 1 group of 3 etc to a maximum 50 people, the results could appearon a seperate worksheet say pasted on to the worksheet starting with the groups of 3 (so paste a group of 3 then skip 3 rows then paste groups of 4 skip 2 rows, the row skipping is to allow seperation and manual entry of extra data). There will never be groups of 5 or more and never less than 3

View 8 Replies View Related

SUMIF Non-contiguous Range

Jan 8, 2006

Range:
C3,C14,C25,C34,C41

Criteria:
>0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the function off.

View 14 Replies View Related

SUMIF Non-contiguous Range

Jan 8, 2006

range:
C3,C14,C25,C34,C41

criteria:
>0

sum-range:
C3,C14,C25,C34,C41

I can't get that to work since the commas in the range are throwing the
function off.

View 13 Replies View Related

Finding Last Row Of Non-Contiguous Range?

Aug 17, 2012

I know how to find the last row of a continuous range. it's simply:

Code:
someRng(someRng.Count).row

How do you find the last row of a non-contiguous range though? Assume the range is vertically laid out and I don't want to do any looping.

View 9 Replies View Related

SQL Queries To Non-contiguous Range

Oct 3, 2009

I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?

Right now the copying happens like this:

If Not rsData.EOF Then
rngTarget.CopyFromRecordset rsData
Else
MsgBox "No records returned.", vbCritical
End If

So should I do
1) One query and move everything in the right places
2) Multiple queries
3) Something else

Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000

View 9 Replies View Related

Find Blank In Non-Contiguous Range

Mar 19, 2013

Need code that will search non-contiguous range for first empty cell, paste data into found cell and data into offset cells and end search. If not empty, move to next cell in non-contiguous range. If NO empties are found in entire range, a msgbox.

Non-contiguous range: Range("B2,B32,B62,B92,B122,B152,B182")

Pasted data: 1st range into found empty, 2nd range into range offset of empty.

View 9 Replies View Related

Vlookup On Non Contiguous Named Range

Jan 20, 2010

I'm trying to do a vlookup on a non contiguous named range.

I have a non contiguous named Range called "temp".

View 6 Replies View Related

Assign A Non Contiguous Range To An Array

Jan 25, 2009

When I insert a line to select the range on the Worksheet it does select the range named as temp. However, the array does fill but only with the first part of the range eg in this case A7:C44.

My aim is to create an array of size point1A( all the rows in the multiple ranges, 3 columns).

Example of a Range to be assigned to the Array point1A. temp = "A7:C44,D7:F44,G7:I44,K7:M44,N7:P44,Q7:S44,"

View 6 Replies View Related

Creating Non-contiguous Range Without Duplicates?

Jun 4, 2012

I am trying to create a large range composed of smaller ranges. Some of these ranges may overlap so I want to make sure when I add cells to the range they don't already exist. I know how to make sure the same value doesn't exist in a range, but not the same cell.

View 2 Replies View Related

Manipulating On A Range Of Contiguous Rows Using VBA

Apr 13, 2008

I have a contiguous range of rows starting from row 5. ...It starts from row 5 but could end anywhere below.

The name of my Sheet is Tester.

Now in Tester, columns B,C, and D have numerical values in them. Using VBA, how can put in values in corresponding row of column A.

So if there are values from B1:D39 and B40 onwards is empty, then A1 will have value B1*C1*D1, then A2 will have B2*C2*D2 and so on until A39 = B39*C39*D39

**I need to write the code so the whole process is done fastest.The code may be long but the macro needs to run as fast as possible.

(Also, I remember using Screen.Updating = False when I wrote similar stuff many yrs ago. Would this be useful here?)

View 9 Replies View Related

Select The Top Right Cell In A Non-contiguous Range

May 29, 2009

Is it possible, through VBA, to select the top right cell in a non-contiguous range? For example, I have defined A1, A3, and A5 as a range. How could I tell Excel to select A5 from within that range?

I am using Excel 2007.

View 9 Replies View Related

Macro: Count Non-Contiguous Range

Dec 17, 2006

I'd like to count non blank cells in 16 separate ranges (each range consists of 6 consecutive cells). The first range in the series is C9:H9. 3 cells are then skipped and the process is repeated with the next range, ie L9:Q9 then skip 3. The last range in the series of 16 is therefore EK9:EP9.

If the value of non blank cells = 2 in any of these 16 ranges then copy the contents of this range to C24:D24 in sheet " Record Form Games 3583". If C24:D24 is not empty copy these values to C25:C26.

If the value of non blank cells = 6 in any of these 16 ranges then copy the contents of this range to C22:H22 in sheet "Record Form Games 3583". If C22 is not empty copy these values to C23:H23.

The ranges colored yellow in the example workbook are the ones that need to be copied to the sheet Record Form Games 3583.

View 9 Replies View Related

Nth Lowest Number In Non-Contiguous Range

Aug 10, 2007

I want to use the Small function on a set of cells that are non-contiguous. But I know that the Small function only works on an array, which cannot contain cell references. Copying the cells to a contiguous range is not an option, as some of the cells I want referenced are generated on the fly in a Sumproduct function. Is there some alternative I could use? I can use the Min function to get the lowest value, as it allows for references, and the Max function to get the greatest, but what about those in between?

View 4 Replies View Related

Copy Non-Contiguous Range & Paste To Last Used Row

Aug 31, 2007

I am trying to have this loop go through colums of data and compare columns 3 and 4. Where there are differences, parts of the row are copied and pasted in a summary table.

My problem is the output to the summary table.

How do I control which cells in the row are copied? (I only want to copy columns 1, 3 & 4)
How do I control where the copied cells are pasted to?

Sub Change()
Dim r As Long

'Loop to Compare Values
For r = 4 To Cells(Rows.Count, 1).End(xlUp).Row
'Looks For Funds That Upgraded
If Cells(r, 4) < Cells(r, 3) Then
'Highlights the row
Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 34
'Copies The Applicable Cell Over Into Column 7
Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1)
'Places a note into column 5
Cells(r, 5) = "UPGRADE"
End If
If Cells(r, 4) > Cells(r, 3) Then
Cells(r, 1).Resize(1, 5).Interior.ColorIndex = 36
Cells(Rows.Count, 7).End(xlUp).Offset(1) = Cells(r, 1)
Cells(r, 5) = "DOWNGRADE"
End If
Next r
Columns("G:G").EntireColumn.AutoFit
End Sub

View 9 Replies View Related

Dsum With Non-contiguous Range Criteria

Dec 28, 2007

Is it possible to use a non-contiguous range for the criteria parameter of the DSUM function? I want to do something like this. =DCOUNTA(wholesale_all,"Assignment Date",D2:D3,G2:G3) This formulas won't work the way I have it typed, but is there a workaround to achieve the same result?

View 2 Replies View Related

Number Of Columns In Non-Contiguous Range

Jun 9, 2008

Consider the following range (in my case a PivotTable):A1:E7

I create a Named Ranges that corresponds to column 1:3 and 6 from the range above. i.e : A1:C7,E1:E7

I need to find out the number of columns of my range, this is because it can change in the future, and I have an offset formula needing this information.

When i type =columns(MyNameRange) it returns #REF! as there are 2 areas in my Named Ranges. I am expecting 4.

I have written the a VBA User Defined Function that does the job, but I would like to know if there is no native solution to get this information.

Function TotColumns(rng As Range)
Dim Cols As Integer
For Each Ar In rng.Areas
Cols = Cols + Ar.Columns.Count
Next
TotColumns = Cols
End Function

View 7 Replies View Related

Define Non Contiguous/Continuous Range

Aug 5, 2008

I am building a macro to conditionally edit cells that meet certain criteria within the current selection:

Dim FinalStr As String
If CheckBox3.Value = True Then
For Each cell In Selection
If cell.Font.ColorIndex = Range2.Font.ColorIndex Then
FinalStr = FinalStr & "," & StrConv(cell.Address, 1)
End If
Next
End If
FinalStr = Right(FinalStr, Len(FinalStr) - 1) 'To remove extra "," at the beginning

What I am getting from this macro is a string like "$I$27,$J$27,$E$28,$F$28" that I use later to edit that range, for instance

Range("$I$27,$J$27,$E$28,$F$28").ClearContents

It worked fine, however, I found later that XL cannot handle more than 20-30 individual cell addresses. The error msg I am getting is: " Method 'Range' of object '_Global' failed " Note that it won't be very practical to pick the cells and edit them within the same loop since there are several criteria times several edit options.

View 9 Replies View Related

Select A Contiguous Range Of Cells In A Column

May 7, 2009

I'm trying to select a contiguous range of cells in a column and then iterate over that. I keep getting error "91", object variable not set. I have:

View 5 Replies View Related

Reference Individual Elements From Non-Contiguous Range Name

Jun 29, 2012

I have several tables on one sheet, each of which has a Grand Total value at the bottom right of the summed data range

I have manually selected (using ctrl + left mouse click) each Grand Total cell and defined all these cells as a Named Range called Grand_Totals

I thought it would be possible to reference each of the cells in the Grand_Totals range individually.

The =Index function works fine if the data is contiguous but I can't figure out how to get anything other than the first element of the Grand_Totals range

So for example

Lets say the cells of the Grand_Totals non-contiguous named range are F10,N10,F30,N30,F50,N50

How would I reference the third element/cell (F30) in this range. I should point out that I actually need to reference all the cells in the Grand_Totals range and the range has a lot more than 6 cells

View 5 Replies View Related

OFFSET Non-Contiguous Named Range In Formula

Jul 14, 2012

I have as the two logical operators for an IF function the following COUNTA calculation which checks how many blanks are in the range GrdMtx7 and subtracts this from the value in E5 and adds the suffix "Module(s)".

Code:

(E5-COUNTA(GrdMtx7)-1)&" Module(s)",(E5-COUNTA(GrdMtx7)&" Module(s)")))
GrdMtx7 is a non-contiguous range consisting of the following cell references on a second sheet:

Code:

='Mod Schedule'!$E$7,'Mod Schedule'!$H$7,'Mod Schedule'!$K$7,'Mod Schedule'!$N$7,'Mod Schedule'!$Q$7,'Mod Schedule'!$T$7,'Mod Schedule'!$W$7,'Mod Schedule'!$Z$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AC$7,'Mod Schedule'!$AF$7,'Mod Schedule'!$AI$7,'Mod Schedule'!$AL$7,'Mod Schedule'!$AO$7,'Mod Schedule'!$AR$7,'Mod Schedule'!$AU$7,'Mod Schedule'!$AX$7
I need for the formula of the IF function to copy down so that it refers to F5, G5, H5, I5...etc.

I also need for the non-contiguous named range GrdMtx7 to refer to the same non-contiguous range shifted down one row intact for the COUNTA function to operate on the next row down, such that COUNTA is checking for blanks in the range:

Code:

='Mod Schedule'!$E$8,'Mod Schedule'!$H$8,'Mod Schedule'!$K$8,'Mod Schedule'!$N$8,'Mod Schedule'!$Q$8,'Mod Schedule'!$T$8,'Mod Schedule'!$W$8,'Mod Schedule'!$Z$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AC$8,'Mod Schedule'!$AF$8,'Mod Schedule'!$AI$8,'Mod Schedule'!$AL$8,'Mod Schedule'!$AO$8,'Mod Schedule'!$AR$8,'Mod Schedule'!$AU$8,'Mod Schedule'!$AX$8

I can't get got to work at all using OFFSET and would rather not create dozens or hundreds of named ranges incrementing one at a time - is there a way to achieve this, preferably with a formula, but vba if absolutely necessary.

My best effort doesn't work:

Code:

(E6-COUNTA(OFFSET(GrdMtx7,1,))-1)&" Module(s)",(E6-COUNTA(OFFSET(GrdMtx7,1,)))&" Module(s)")

View 2 Replies View Related

Rows Contained In Non-Contiguous Named Range

Dec 1, 2012

I'm trying to write a 'ReImport' macro to copy data from one model into another. I'll call these models 'Master' and 'Split'. 'Master' contains about 50 departments' worth of financial data, and the 'Split' files contain a Division's (between 1 and 20 departments') worth of data. Structurally, the 'Split' files are just copies of the Master with the unneeded departments hidden. They are sent to various Divisions for completion, then the ReImport process takes data from the Split files and copies it back into the Master. Every Split File contains the full 50 departments from the Master, and it is only the 'Visible Row' distinction plus a 'ReImport key' that should determine what gets ReImported.

Each 'Split' range has a range name that controls what Departments are hidden or visible, but the range name is not contiguous. In other words, Division A might show Departments 1, 4-8 and 23. (Reordering the Depts is not an option.)

In a perfect world, I want to evaluate ONLY rows contained within the Division range name for the ReImport key. But the non-contiguous aspect of the named range is throwing me off. So in the above example, JUST Departments 1, 4-8 and 23.

My second-best solution would be to search through Departments 1-23, though that would be much slower...

View 5 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

Macro: Copy Non-Contiguous Range Of Cells

Aug 24, 2008

I want to be able to copy cell D4, I4, G10, G12 etc from one spreadsheet and paste into another spreadsheet in the same locations as the source.
Is there a macro that will do this?

View 9 Replies View Related

Insert Formula Into Non Contiguous Range Via VBA Macro

May 29, 2009

I have recorded a macro code for which is as follows. This Macro goes into a worksheet and gives percentile value for a range of data. But this is becoming cubersome as this sheet is 65531 rows and can span multiple spread sheets. Can this be put in a loop of some sort to go through the whole worksheet? ....

View 9 Replies View Related

Conditional Minimum With Non Contiguous Range & Hide If Zero

Oct 25, 2007

I have 3 columns L, N and P

I would like to return the MIN value(column R) between the columns
except where the MIN = zero then just leave the result as blank.

View 9 Replies View Related







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