Loop Through Rows Of A Union Range

Aug 19, 2006

'How do I use a FOR NEXT loop on a union to just list all cell values in Col A?

'Just need a little help getting a handle on ranges and the union of ranges.
' This is my first time posting a question so any patience is appreciated.


Dim r1 As Range
Dim r2 As Range
Dim myMultipleRange As Range
Dim xCell As Range

Set r1 = Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
Set r2 = Sheets("Sheet1").Range("B1", Range("B65536").End(xlUp))
Set myMultipleRange = Union(r1, r2)

r1.Select

For Each xCell In r1
'Ive been using this type of code to access anything I need from a sheet.
Debug.Print r1(xCell.Row, 1), r1(xCell.Row, 2), r1(xCell.Row, 3)
Next xCell...............

View 9 Replies


ADVERTISEMENT

Finding The End Of Rows And Columns And Taking Union Of Sets

Jun 13, 2013

I have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers. So I have written the following code, that search through the folder, converts text to numbers and colses the file.

Sub ConvertText2NumberFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C: ...analysis"

[Code]....

The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows. So I wanted to use the union. I wrote the following:

Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
Application.Union(Range("IndexColumns"), Range("IndexRows")).Select

Which doesn't work! Morover it is only for one column and one row.

NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
NOTE2 : The lenght of indices is different from one file to another

View 2 Replies View Related

Union Result Does Not Include Second Range?

Aug 15, 2014

I'm writing a function which includes the need to merge 2 ranges into 1 new Range object. The problem is that adding a breakpoint just after this occurs, and viewing the immediates, the new range only actually contains the first input range

For testing, and displaying here I've placed the 2 sub-Ranges into temp objects

[Code]....

The breakpoint is on the next line (not shown), so I can view all the Locals

tempyR1 and tempyR2 both are Range objects containing the correct Range data as expected from the code
However, objExcelRange only contains exactly the same as tempyR2

I'm using the Locals to check as this new combined Range will undergo further processing before being placed onto a sheet, and the combined Range will actually be generated dynamically depending on what options the user picks from a second sheet within the WorkBook - which could in fact be created from between 2 and 5 sub-Ranges

View 7 Replies View Related

Union Range Comprised Of Two Cells On Worksheet

May 19, 2014

I am trying to create a union range that is comprised of two cells that are no where near each other on a worksheet. I keep getting the object required error. Everything else works perfectly.

Dim myRng As Range
Set myRng = Application.Union((Range("IndData1")), (Range("IndData2"))).Activate
Do Until ActiveCell = ""
Selection.Copy

[Code] .....

View 1 Replies View Related

Loop Through Rows In A Range

Oct 24, 2007

I am reviewing a macro someone left at a client, but I am stuck on the rowcounter declaration.

View 7 Replies View Related

Loop Range Using Variables For Rows

Dec 16, 2006

I need to write a FOR... Next loop that will loop through a range of rows, select the same cell range on each row, perform a task, then advance to the next row. I can't seem to figure out how to substitute the loop counter for the row index. For example:

For counter = 1 To 100

Range("B(counter)","F(counter)").Select
ActiveCell.FormulaR1C1 = "1"

Next counter

View 3 Replies View Related

Loop To Calculate Rows In Selected Range In Groups Not All At Once

Oct 9, 2008

How do I divide a range (number of rows will vary) into groups for calculation purposes?

I have a spreadsheet with too many dependencies to calculate properly (over 400,000). After I calculate I can delete the empty rows, and then there will only be a few hundred rows left and it will calculate just fine.

I think all I need is a loop that will break the range into groups and calculate the groups separately, but I'm very bad at loops.

This is what I need to break into workable parts.


Sub FilterTrue()
ActiveSheet.Calculate
Range(Range("AC16"), Range("AC16").End(xlDown)).Select
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
Selection.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Selection.AutoFilter
End Sub

View 9 Replies View Related

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

I have working code that returns a row number within a for loop based on parameters I set.

Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.

Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*

Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.

All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.

View 4 Replies View Related

Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range

Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

View 13 Replies View Related

VBA Command Loop Through Spreadsheet Rows Until Blank Rows

Apr 22, 2012

Using excel's text to speech I've put together a basic spreadsheet.

[URL]

Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech

[Code]....

View 9 Replies View Related

Add To Selection By Right Clicking And Union

Jul 15, 2014

I'm trying to make a code so that when optionbutton1 is checked, it allows you to right click to select a cell, then right click on a different cell and select that cell as well as the previous cell, etc. Here's What I have so far.

[Code] .....

View 6 Replies View Related

With For Multiple Ranges (not Using Union)

Dec 29, 2008

Quick question: When using "With" is it possible to specify 2 ranges, or will I need to do 2 different with blocks? The reason I say not using Union is because I need my ranges seperate. I run 1 set of commands on Range 1, and 1 set of commands on both Range 1 and 2.

View 3 Replies View Related

Union Named Ranges

Apr 29, 2009

(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:

View 6 Replies View Related

Union Ranges On Two Different Sheets

Mar 3, 2008

I'm trying to union two different ranges that exist on two different sheets and then copy the unique values into a third range with just the unique values. I was going to then use the unique range as my rowsource in a listbox. This is the code I was working with so far but i'm getting a compile error (method range of object failure).

Private Sub UserForm_Initialize()
Dim range4 As Range
Dim range3 As Range
Dim range2 As Range
Dim range1 As Range
Set range1 = Worksheets(1).Range("MyRange")
Set range2 = Worksheets(2).Range("MyRange2")
Application.Union(Range("range1"), Range("range2")).Select
Selection = range3
range3.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=range4, Unique:=True
Me.lstone.RowSource = range4
End Sub

View 9 Replies View Related

Selecting Ranges On Different Sheet Using Union

Nov 7, 2013

I am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.

VB:
Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))

[Code] ....

View 1 Replies View Related

Trying To UNION Same Cell And Blank Cells Possible?

Nov 12, 2012

I'm trying to select the same cell and a blank cell multiple with a UNION(). Is this possible?

Here's a summary of my code:

Code:
Dim c
Dim Rng As Range
Dim blankC As Range

Set blankC = Range("XFD1048576")

Code] ......

Ultimately, I'm trying to copy various cells and reorganize them including blank cells to conveniently use UNION for a quick copy and paste as opposed to explicitly defining where to paste everything.

View 9 Replies View Related

Checking If Selected Cells Is A Union

Aug 24, 2013

I would like to check if selected cells is a union of several ranges or one solid range. I am sure there is a way, but did not manage to find it. How to do it?

View 2 Replies View Related

Union Selection Without Looping Through Cells?

Apr 6, 2014

how one can union a selection without looping through the cells? I need to write a noncontinuous range into an array, and it can't be sorted as I'm using specialcells(xlCellTypeVisible).

View 1 Replies View Related

Object Required: Union Method

Nov 9, 2006

columnA contains account numbers sorted in ascending order i need to use the union method to select all rows where the account number changes so that later i can insert rows between different account numbers in one action. i tried to use the following code but an error occurs stating 424 object required ....

View 7 Replies View Related

Union Function For Multiple Worksheets

Jun 1, 2007

I have two worksheets sheet1 & sheet2 in Workbook3 both get data from two different workbook1 & workbook2 using Microsoft Queries. Column Headers are same but contentes and no. of rows are different in these sheets. I would like to combine data from these sheets into sheet3 of Workbook3 so that i can create pivot table on that. I tried using Union Method, But it gave me "Run time error '1004'. Method 'Union' of object '_Application' failed in the following line

Set myMultipleRange = Application.Union(r1, r2)

Union method works fine when combining ranges from the same worksheet.

Multiple consolidation in Pivot table is not desirable to me as i have to group data using two columns and i could not get what i wanted using two page fields.

I have created named ranges for each sheet data. Is there an alternative to Union method for combining these named ranges from two worksheets into third sheet through VB code?

View 5 Replies View Related

Problem With Union Method: Combining Two Ranges

Oct 7, 2008

I have the following two ranges in the same spreadsheet: A1:A1000 and B1:B1000. Both contain data that I would like to manipulate. Let's assume I would like to add "1" to every cell in the two ranges. This is what I have so far:



Sub Test2()
Dim wb As Workbook
Dim ws1, ws2 As Worksheet
Dim rng1a, rng1b, rng_union As Range
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("testsheet")
Set ws2 = wb.Worksheets("result")
ws1.Select
Set rng1 = Range(Cells(1, 1), Cells(1000, 1))
Set rng2 = Range(Cells(1, 2), Cells(1000, 2))

Set rng_union = Union(rng1, rng2)...................

Function ADDONE(rng)
n = rng.Cells.Count
ReDim rng_add(1 To n)
For i = 1 To n
If rng(i) = "" Then GoTo NextIndex
rng_add(i) = rng(i) + 1.............
End Function
I have to somehow combine the two ranges because the data needs to be manipulated simultaneously. Instead of having the range of results returned to me (see worksheetfunction.transpose), I could have calculated the variance, average etc... of the results, too. I'm using the transpose function to show that the operations in function ADDONE are only conducted for the first range of cells (rng1) that I have joined with the union method. I presume that Union might not be the correct method but I don't know ehich other method to use.

View 9 Replies View Related

Chart Source Data: Union Of Every N Cell

Sep 19, 2006

I have 4 sheets. In each sheet, I have a column of data for every hour of every day of one year.

In the 1st worksheet, I want to make a chart using :

- for Y values : the average of the data for each day
- for X values: the day

In the 3 other worksheets,
- for Y values : the data for each hour
- for X values: the hour

To make it more simple, I will first build in the "D" column the data of the average data for each day (so, every 24 columns)

Here is my

Dim lNbSheet As Long ' sheet number
Dim rSheetData As Range ' what will be my Y values
Dim rSheetTime As Range ' what will be my X values
Dim sSheetName As String ' current sheet

For lNbSheet = 1 To 4

'selection of the Y values
Sheets(lNbSheet).Select
sSheetName = ActiveSheet.Name

I get the error "methode SeriesCollectiosn of Object _chart failed;

Also, if you have an idea that could make me avoid writing all the new average-for-the-day data in column D (using it directly),

View 8 Replies View Related

Create Pivot Table Based On Union Query

Jul 2, 2010

I created a union query in Access to join two tables (Projections and Actual Sales). The query produces the results I want. I need to create a pivot table in Excel using the union query as the source. When I pull up the data import function in Excel, the union query does not appear. Do I need to do something else? I have tried to create a select query where I select all from the union query and I can find that fine.

When I use this query to create the pivot table the results end up all zeros when I try to sum the values. It creates some crazy results when I show it as count of also.

I can provide the data in either the Access database or Excel spreadsheet.

View 4 Replies View Related

Excel 2007 :: Union Of Values In 2 Columns To One In Increasing Order?

Jul 20, 2012

I have to find the union of 2 columns in excel and club those 2 columns into a single column with values in the increasing order.eg: column 1- 0 2 4.. , column 2- 1,3,5.. final result in column 3 should be 0 1 2 3 4 5...plz let me know the code for this that i can run in VB editor(- excel 2007)

View 9 Replies View Related

Loop To Add Rows

Jun 15, 2007

I am automating a spreadsheet. I want to be able to take all values of product 1 from column a and then add the corresponding values of units and dollars in the adjacent columns.

So I would like a list with each product at the bottom, and corresponding units and dollars, yes I know I can use formulas and pivot tables, but I would like to automate this

The problem is that my loop works for the first product, but it will not go again for the next one.

Here is my code and spreadsheet with code in it, try it to see

Sub rowadd()

Sheets(1).Activate

Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
If IsEmpty(LastCell) Then
'do nothing
Else
Set LastCell = LastCell.Offset(1, 0)
End If
End With

View 9 Replies View Related

Vlookup To Loop Through Rows?

Feb 18, 2014

My workbook contains four sheet: input, letter 1, letter 2, data

Data contains customer records, column A is numbers 1-500

Input sheet is a customer form containing details for 1 customer. The customer details are pulled from the data tab using vlookups based on a value in cell a1 on the input tab.

Letters 1 & 2 are letters to a customer. They again use vlookups to populate various fields on the letters with the customer information on the input sheet.

What I need:

A macro to populate the input form by working down the records on the data sheet. when the input form is populated with customer information it is to export the letters as a pdf and then move on to the next row of customer details.

I have the export to pdf macro, i just cannot get the input form to loop through the customer records..

View 1 Replies View Related

Loop For Deleting Rows

Apr 20, 2009

Loop for Deleting Rows
Whats wrong with my code ?

View 3 Replies View Related

Eliminate Rows (loop To The End Of Each Row)

Jul 13, 2009

I'm trying to write a macro that will loop to the end of each row, if the last cell is a number it will cut and paste that whole row into another sheet, if the last cell is a word then it will leave it as it is.

View 3 Replies View Related

VBA Loop And Delete 15 Rows

Sep 10, 2013

I have output from mainframe that is copy/paste to an excel workbook. I need to have VBA loop and delete the 15 rows. This is mainframe output so it will always be 15 rows

Count
Campus
Name
Course
Start Date

1
336001
Student1
1
9/2/2003

[Code] ............

View 1 Replies View Related

Macro Loop Not Using All Rows

Nov 9, 2006

I've taken over a spreadsheet that has a macro in it. However it doesn't seen to function properly. The big issue with the Macro is that it is only running for the first 80 lines of data. I can't have a restricted range as the number of lines could increase/decrease each month depending on activity. I've attached the code.

Sub Macro1()
Dim strTemp As String
Dim intSpace As Integer
Dim intZero As Integer
Dim i As Integer
Dim strCat As String
Dim strZero As String
Dim intLoopCounter As Integer
Dim rw, this
intLoopCounter = 1
For Each rw In Worksheets(1).Cells(1, 1). CurrentRegion.Rows
this = rw.Cells(1, 1).Value ...................

View 8 Replies View Related







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