Select Non Contiguous Ranges Via Macro, Using Variables

Apr 29, 2008

trying to select multiple ranges of data at once using variables as my selection range criteria.

I.e., I want something similar to:

Range("A10:A30,B10:B30,E10:E30").Select

But would like to be able to perform the same selection using variables.

StartVar = 10, EndVar = 30

I'm sure it's just a matter of syntax, but I can't seem to get it right.

View 3 Replies


ADVERTISEMENT

VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges

Nov 10, 2008

I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is

Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName

It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.

View 4 Replies View Related

Sorting Non Contiguous Ranges

Mar 23, 2009

I have a spreadsheet that I have developed at work to track sales related data. As part of the reporting for this data, it is sorted by date. I have been asked to capture some additional related data and due to the structure of the spreadsheet(that has been in use several months with no issues) I can not put the additional data in adjacent columns without doing a complete redesign of the format.

I can place the data several columns away.... The data "in the middle" is static and is used for other calculations and should not be sorted. Is there a way to sort both ranges of data WITHOUT disturbing the columns separating them and keep the proper relationship with the data?

See attached for a very simple example. In this data set the "original" data was in range A2:E11 the "New" data is in Range I2:M11. It would make life simple if I could do something like this (but it sorts everything in between)

View 2 Replies View Related

Compare Non-Contiguous Ranges

Jun 21, 2007

how to Determine two uncontinnuous ranges are same? "Address" can not work here.

Sub Macro1()
Set Rng1 = Range("H1:H10,C1:C10,F1:F10")
Set Rng2 = Range("F1:F10,C1:C10,H1:H10")
Debug.Print Rng1.Address
Debug.Print Rng2.Address
If Rng1.Address = Rng2.Address Then
Debug.Print "yes"
Else
Debug.Print "no"
End If
End Sub

View 9 Replies View Related

Count #N/A In Non-contiguous Ranges

Sep 12, 2007

I want to count the number of cells with a “#n/a” in for a cell range which is non-continuous. For example my cell range is: “H5,J5,L5,N5,P5,R5,T5,V5". I’ve tried a few different things but I can’t get the function to work.

View 8 Replies View Related

COUNTIF On Non Contiguous Ranges

Feb 22, 2008

I'm creating a coversheet that shows the percentage of "yes" and "no" answers from several cells in other sheets of the book. My problem is that I'm trying to use a countif statement to do it, and the cells i'm counting aren't in a simple range. ex. A1:A10

In stead i'm trying to get something like:
=COUNTIF((Sheet2!A2,Sheet2!A10,Sheet2!A15),"yes")

View 2 Replies View Related

Clear Non-Contiguous Ranges

Feb 28, 2008

I'm getting an error on Range("AB6:AS12,..."). The error is: "1004 Runtime error, Method 'Range' of object '_Global' failed". My code should copy the values from place A to B. Then clear the content of B. Heres my code:

Sub Ny_uke()
' copy cells
Range("B21:K50").Value = Range("AX21:BG50").Value
Range("B54:K83").Value = Range("AX54:BG83").Value
Range("B87:K116").Value = Range("AX87:BG116").Value
Range("B120:K149").Value = Range("AX120:BG149").Value
Range("B153:K182").Value = Range("AX153:BG182").Value
Range("B186:K215").Value = Range("AX186:BG215").Value
Range("B219:K248").Value = Range("AX219:BG248").Value
' clear content of cells
Range("AB6:AS12,B21:K50,B54:K83,B87:K116,B120:K149,B153:K182,B186:K215,B219:K248").Clear
End Sub

When the error occurs I can press 'Debug' and continue the script whiteout changing the code and the code will continue without any errors. If I place the line 'Range("...")' on top of the script no error occurs. I I split the 'Range("...").Clear' into several Range("AB6:AS12").Clear the problem solves, but this produces several unwanted lines of code.

View 7 Replies View Related

Coding Non Contiguous Print Ranges

Dec 28, 2009

What is the best way to declare non contiguos print ranges?

For instance I have the following
Sheets("Report").Range("C17:C33").Copy
But I want to add other print ranges like J5:K5

View 9 Replies View Related

Pass Values Between Two Non Contiguous Ranges

Nov 20, 2008

I have Workbooks("A") and Workbooks("B") open.

Workbooks("A") contains Sheets("Sheet1"). Range("mySource").
Workbooks("B") contains Sheets("Sheet1").Range("myTarget").

Both ranges have been created by joining multiple ranges, in this way:

Union(Range("C1:C13"), Range("K1:K2"), Range("K5:K9"), Range("K14"), Range("Q6"), _
Range("I18"), Range("B20:P20"), Range("B24"), Range("C26:E26"), Range("C29"), _
Range("B34:B40"), Range("B44:Q50")).Name = "mySource" 'or "myTarget"

So: both ranges contain the same number of cells with the same addresses, and they have been added in the same order. However, if now I try to pass all values from mySource to myTarget, in this way:

Workbooks("B").Sheets("Sheet1").Range("myTarget").Value = _
Workbooks("A").Sheets("Sheet1").Range("mySource").Value

the result is a complete mess. Only the first "subrange" of mySource ("C1:C13") is passed to myTarget, and pasted in each of its "subranges", sometimes by rows and sometimes by columns...

View 2 Replies View Related

Merge Multiple Non-Contiguous Ranges

Jul 20, 2007

if there is a way to select a group of cells, select another group of cells and keep the previously group of cells selected.

Example: ...

View 6 Replies View Related

Replace Different Values Within Non-Contiguous Ranges

May 8, 2008

I had some code working fine in office 2003:

Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub

the problem is that it's not working for Office 2000

View 2 Replies View Related

Replace Same Value Within Non-Contiguous Ranges With Different Values

May 18, 2008

This time I want to replace:
number "99" in range("B:C") to number "1";
number "99" in range("E:F") to number "2";
number "99" in range("H:I") to number "3";

Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub

View 2 Replies View Related

Custom Sort Non-Contiguous Ranges

May 21, 2008

can anyone modify below macro to sort this data:

before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc

after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc

Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")

With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub

View 9 Replies View Related

Programming A Macro To Select Row Ranges Based On A Formula

May 2, 2009

I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document.

If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:

View 4 Replies View Related

Load Array With Values From Non-contiguous Ranges

Aug 2, 2014

I have tried:

Code:
With Sheet1
x = .[b7:i16,b19:i23,b27:i32].Value
But that only loads the first range (.[b7:i16]).

This does work:

Code:
.[b7:i16,b19:i23,b27:i32].Copy .[z1]: x = .[z1:ag21]: .[z1:ag21].Clear

I would rather avoid having to copy/paste, load the array, then clear. Is there a way to load the array directly?

View 2 Replies View Related

Copy And Paste Special Values For Non Contiguous Ranges?

Mar 18, 2014

I use to have a macro that could copy formulas from non contiguous ranges and paste the values in the same cells. So for example, I could select a non contiguous range like B5:B10, E10:F12, and G1:G10. The macro would then copy and paste the values in those 3 ranges, so those 3 ranges would be values instead of formulas.

I think it worked by storing each range and then looping through each range to copy and paste special values.

View 2 Replies View Related

Dynamic Print Ranges With Multiple Non-contiguous Cells?

Jan 24, 2012

I have a worksheet that has a few ranges and I need a printarea statement that looks like this:

Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"

The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.

So, I tried something like this:

Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD

But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.

View 9 Replies View Related

Transpose Combobox Lists From Multiple Non-contiguous Ranges

Jul 29, 2008

I transposed them into a separate sheet and used that for my lists. I have to share the file with macs.

My pc is on Vista running Excel 2003. The macs are OSX Panther (not Leopard) and running Excel 2003.

Solution (?): if I make the combobox lists programmatically, it will work on the macs?

The UserForm1 with 3 comboboxes:
The comboboxes are not dependent on each other.
For each combobox, I need to have 2 columns.
For each column, I need to transpose 2 x ranges (they are NOT contiguous)

My attempts have been embarassing and futile.

What it should be: ....

View 9 Replies View Related

Tick Cell Upon Selection. Restrict To Non Contiguous Ranges

Apr 2, 2008

I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub

How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.

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

Pass Multiple Non-contiguous Ranges Into A User Defined Function

Dec 23, 2009

I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.


Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant

If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")

Denominator = 0............

View 9 Replies View Related

Restrict Worksheet Change Event To Multiple Non Contiguous Ranges

Nov 20, 2007

Can you have more than 1 worksheet change event on the same worksheet, if so, how do you name it to prevent the ambiguous name error. What code would I need to select a text value in colums e11:e15 based on the cell value in cell named STATE and place the selected value in cell e16. I have, thanks to this resource, one worksheet change event that selects a numeric value from any column E3,F3:F7 and places that value in cell C4. but the same code doesn't work for the new worksheet change event.

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

Select Non Contiguous Blank Rows Via Array

Feb 17, 2008

I have a problem with the following code.

I want to select multiple rows in a very huge spreadsheet. But, when I
use this code, I receive an error: Runtime error 1004, Method ' Range'
of Object '_global' failed. What is wrong? Or maybe there is other
better way to select automatically empty rows in a spreadsheet?

Public Sub delhol()
'
' delhol Macro
'
' Keyboard Shortcut: Ctrl+d
'

Dim k As Variant
Dim rr As String

k = Array(34, 35, 38, 39, 40, 77, 133, 182, 207, 209, 225, 226, 295,
299, 300, 338, 394, 437, 468, 470, 480, 481, 560, _
591, 599, 655, 712, 729, 746, 755, 756, 852, 860, 962, 990, 1005,
1006, 1077, 1081, 1082, 1083, 1114, 1176, _
1217, 1252, 1260, 1261, 1338, 1342, 1343, 1344, 1375, 1381, 1437,

View 4 Replies View Related

How To Select An Entire Range Of Non-Contiguous Cells In A Column

Aug 19, 2009

I recently found this code for selecting a whole column of non continuous cells.

ActiveSheet.Range("a1",ActiveSheet.Range("a65536").End(xlUp)).Select
How can I change the "a1" & "a65536" so it can work and be activecell instead?

View 9 Replies View Related

Reference Cells & Ranges With Numeric Variables

Jun 13, 2008

I have the following macro, which works fine (extraneous code not shown)...

For I = Start_ROW To Last_ROW_Z
Range("A" & I & " : " & "O" & I).Copy

But I want to make the "O" column identifier dynamic. So I've defined the column with the dimension...

Last_COL_Tracking = Sheets(" Tracking").Range("IV" & Start_ROW).End(xlToLeft).Column - 1

But this returns a column number, not the letter, so I can't use it in my original formula.

I tried converting to R1C1 with the formula...

Range ("R[" & I & "]C[1]:R[" & I & "]C[" & Last_COL_Tracking & "]")

...but this errors out.

View 3 Replies View Related

Date Between Multiple Non Contiguous Date Ranges On Another Sheet?

Nov 8, 2012

I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,

I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.

I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.

=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")

It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.

The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.

Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!

I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.

I hope this becomes a bit clearer on the sheet I have attached,

View 9 Replies View Related

VBA Match Function With Variables / Number And Alternating Ranges

Jun 2, 2014

I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.

ExampleFile.xlsm

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

Select An Excel Sheet With 2 Variables In It?

May 17, 2013

So far my line of code is:

Sheets(" & Cstr(y) & " - " & Cstr(i) & " load" & ").Select"

Selecting a worksheet with 2 variables in it and some text,where y and i are integer variables. P

View 1 Replies View Related







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