Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Macro: Copy Non-Contiguous Range Of Cells

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Copy Non-Contiguous Range & Paste To Last Used Row
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
End Sub

View Replies!   View Related
Copy Contiguous Cells & Paste To Every Second Row
I need to make the data in cells f4:f7 skip a row as shown in column D. How can I do this without doing a long and tedious cut and paste as I have over two years of data to reformat. Is there a code or some type of auto-format setting I could use?

View Replies!   View Related
Copy Non-Contiguous Cells In Selection
I have a spreadsheet named PRODUCTS and in row A there are a bunch of product codes. I would like for the user to be able to use the CTRL and mouse to select all the products that they want and then once they push that button I would like it to paste the user selected cells into a sheet called Quote starting at A7.

View Replies!   View Related
Macro: Count Non-Contiguous Range
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 Replies!   View Related
Select A Contiguous Range Of Cells In A Column
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 Replies!   View Related
Check Non Contiguous Range To See If All Cells Are Filled
Is there a way to prevent a workbook from closing or being submitted until information has been entered into the following cells? B78, B80, B82, B84, B86, B88, B90, B92, B94, B96, B98, B100, B102, B104, and B106?

View Replies!   View Related
Check For Empty Cells In Non Contiguous Range
The application reads in a file, whcih can have various formats. To check which format it's in, I plan to look for certain empty fields/ cells. I can successfully detect a group of empty cells by explicitly testing each one, but when I put them all in a range and test that, the check fails. So far I've reduced the problem to the following example code.

Sub check_clear()
If IsEmpty(Range("a1")) Then
Range("g2") = "A1 empty"
End If
If IsEmpty(Range("b1")) Then
Range("g3") = "B1 empty"
End If
If IsEmpty(Range("c1")) Then
Range("g4") = "C1 empty"
End If
If IsEmpty(Range("d1")) Then
Range("g5") = "D1 empty"
End If..............

The result is that each individual cell check results in the relevant "XX empty" message. However, the test that the range of multiple cells is empty never produces a result. I'd really like to understand the underlying reason - as well as find out how to perform an isEmpty test on a range. I'm looking more for guidance and insight than a canned solution

View Replies!   View Related
Insert Formula Into Non Contiguous Range Via VBA Macro
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 Replies!   View Related
How To Select An Entire Range Of Non-Contiguous Cells In A Column
I recently found this code for selecting a whole column of non continuous cells.

How can I change the "a1" & "a65536" so it can work and be activecell instead?

View Replies!   View Related
Macro Challenge-Copy Range Of Non Empty Cells From 2 Sheets
first time posting. Need some help. I have a workbook with two sheets - (say sheet A and sheet B) that I need to copy a range from and paste (concatonated) into a new workbook. The thing is I want to copy the "non-empty" cells in columns d and e of each sheet - another words - I dont want to specify a specific cell range because users will be periodically adding new rows. Then I want to paste the "combined" from both sheets into a new sheet in a new workbook.

View Replies!   View Related
Copy Non-Contiguous Columns
how to reference an entire column only knowing the column numbers, not letters. The response below was given.

Range(Cells(1, 40), Cells(1, 44)).EntireColumn.Select

I have a similar situation, except that I use the FIND command to return a column number based on a string match.

X_Column = Sheets ("Sheet1").Range("A1:IV1").Find("X").Column
Y_Column = Sheets ("Sheet1").Range("A1:IV1").Find("Y").Column
Z_Column = Sheets ("Sheet1").Range("A1:IV1").Find("Z").Column

Because of this, I can recieve non-sequential column numbers, simply based on where "X", "Y" or "Z" are in the sheet, which can be anywhere. I would like to select X_Column, Y_Column, and Z_Column and copy/paste to another sheet, hopefully without having to loop. I have a feeling that an answer refering to Named Ranges is coming, but I'm very green on the subject.

View Replies!   View Related
SUMIF Non-contiguous Range



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

View Replies!   View Related
SQL Queries To Non-contiguous Range
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
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 Replies!   View Related
Resizing Non-contiguous Range
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 Replies!   View Related
Grouping Contiguous List Into Non-Contiguous Groups
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 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 Replies!   View Related
Select The Top Right Cell In A Non-contiguous Range
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 Replies!   View Related
Vlookup On Non Contiguous Named Range
I'm trying to do a vlookup on a non contiguous named range.

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

View Replies!   View Related
Manipulating On A Range Of Contiguous Rows Using VBA
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 Replies!   View Related
Define Non Contiguous/Continuous Range
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
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


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 Replies!   View Related
Assign A Non Contiguous Range To An Array
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 Replies!   View Related
Number Of Columns In Non-Contiguous Range
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
TotColumns = Cols
End Function

View Replies!   View Related
Dsum With Non-contiguous Range Criteria
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 Replies!   View Related
Nth Lowest Number In Non-Contiguous Range
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 Replies!   View Related
Min Value Excluding 0 For Non-contiguous Cells
I can find the min value excluding zero in a range of contiguous cells, but how can I do it if the cells are A1, A3, A25, A67, etc?

View Replies!   View Related
Clear Non-contiguous Cells
Am looking for routine that can be used to delete the contents of cells from multiple, non-contiguous cells throughout a single worksheet. I would like to place a "button" or some form of toggle-switch on the worksheet that will clear all entries at once. I see a thread "Macro Deleting Wrong Cells" that references a VBA sub-routine called "DelCells". I happen to be running Excel 2007 and am still adjusting to the new menu "structure" :-)

View Replies!   View Related
Sort Non-contiguous Cells
How would I go about sorting a selection of non-contiguous cells?

View Replies!   View Related
Conditional Minimum With Non Contiguous Range & Hide If Zero
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 Replies!   View Related
Paste Non Contiguous Range As Values To Last Used Cell In Row
I am trying to copy paste info from one sheet to another, however, I would like to select where the information is to be pasted, not have it go to a predetermined cell or column.

Here is the code I have so far that works fine, but it requires a macro for each column I would like to paste to.....

View Replies!   View Related
Hide Blank Rows In Non Contiguous Range
How do you select certain ranges in the same column to filter? The ranges I need are "c36:c50" & "c54:c68" & "c72:c87" & "c91:c155" & "c158:c172" & "c176:c202" all filtering for blanks. I can not filter from c36:c202 because there are blanks in the missing rows and they have to stay.

View Replies!   View Related
Reference Non-Contiguous Named Range In Function
I have a split named range covering non-contiguous areas, ie:
Name: status_web
Range: =$F$14:$I$122,$F$700:$I$746,$F$798:$I$830,$F$905:$I$933

This appears to work fine when selecting the name however it returns a #VALUE! error when using it in a formula, eg: = COUNTIF(status_web, "*p*"). Is there any way to get this to work, or do I need a separate named range for each contiguous section? NOTE: Not sure whether this extra fact would through a spanner in the works, but I am referencing this named range from a separate workbook.

View Replies!   View Related
Export Non-Contiguous Column Cells
I have a spreadsheet with multiple columns. I need to save values from each specific column, but they are not non-contiguous, to a .txt or .csv file. In other words, I may need B:10, B15, B12, B25, etc. saved as one string in a text file. I have been working on this for about 15 hours and can't seem to get it to work.

View Replies!   View Related
Conditionally Summing Non-contiguous Cells
Lets say I have values in A1, A3 and A5 and I want to to build either a sumif or an sum if array formula to conditionally sum them. How do I do this, assuming I don't want to use VBA or named ranges, i.e. create an array of noncontiguous cells and evalute & sum them.

View Replies!   View Related
Find Highest Value In Non-contiguous Cells..
I have a cricket excel sheet that contains batsmans scores over a season. These scores are kept in non-contiguous cells for each game (ie. D5, J5, P5, V5 etc..) In the cell next to the score is an option for the user to enter an '*' to denote a not out score (these are in E5, K5, Q5, W5 etc.)

I can sort out a formula to find the highest score (from D5, J5, P5, V5) and place this high score in a cell elsewhere - but what I really need to do is to check if the high score is not out by looking to see if there is an asterisk in the adjacent cell, and then place the score AND asterisk in another cell.

View Replies!   View Related
Average Non-Contiguous Cells & Multiply
I've tried hard to come up with a title as accurate as possible. Here is my problem:

I have six cells that need to be summed (N9,Q9,T9,W9,Z9,AC9). The sum of the cells needs to be multiplied by however many cells that have numerical data in them divided by 6. Assuming all cells in the range (N9,Q9,T9,W9,Z9,AC9) have the value of 50 the following rules would apply: ...

View Replies!   View Related
Add Items: Multi Column ListBox: Non-Contiguous Range
i have several listbox with two columns in a userform in a sheet i have several columns whith values and i'm trying to put the values from the columns in the respective listbox. down is the code i wrote but is stressing with column command

Private Sub UserForm_Initialize()
Dim contar, j, i As Double
contar = 0
For j = 1 To 15
linha = j
For i = 3 To 65536

View Replies!   View Related
Restrict Worksheet Change Event: Non-Contiguous Range
is it possible to have a userform activated as a result of a Worksheet_Change event on more than 2 cells. Currently I have the code below which will activate userforms if either a cell in Column G or a cell in Column T is changed.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G:G", "T:T")) Is Nothing Then Exit Sub
If Intersect(Target, Range("G:G", "T:T")) = "" Then Exit Sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
GlngRow = Target.Row
Call Module3.ShowList1
End If
If Not Intersect(Target, Range("T:T")) Is Nothing Then
GlngRow = Target.Row
Call Module3.showlist4
End If
End Sub

What i need to do is activate another userform if a cell in Column AC is changed. I tried this code but got an error "Compile Error: Wrong number of arguments or invalid property assignment", which leads me to believe I can't add any more cells to the argument and it highlights the word Range in this line - If Intersect(Target, Range("G:G", "T:T", "AC:AC")) Is Nothing Then Exit Sub..........

View Replies!   View Related
Non-contiguous Print Area Macro
I have a document which will require signatures after printed and want to keep the signature portion at the bottom of the page (currently rows 102-104). The problem is that the document will not need to be that long for every person and was wondering if it would be possible to have a macro that checks (C7:C:100), stops printing when it reaches the blank cells, and then prints those last 2 rows?

View Replies!   View Related
Extend Non-contiguous Row Selection In Macro
I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines.

I convert this file into a txt file.

I then create a macro to import it as space delimited and format it.

What I need to do it get rid of the header/footer information on EVERY page.

When the txt file is created it doesn't have any page marks.

The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.)

Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?

View Replies!   View Related
Sum Cells Which Meet Certain Values In Non-contiguous Columns
I'm trying to sum cells which meet certain values in non-contiguous columns. It's difficult to explain so I've attached a sample wookbook.

View Replies!   View Related
Average Non-contiguous Cells & Exclude Zeros
I would like to average a non-continuous range of cells while also excluding all zeros.

I am averaging hours worked and the hours are found in cells:

B2, D2, F2, H2, J2, L2, N2

Some of these cells contain zeros at this time and I do not what to include the zeros in the average.

I have found formulas that would work but they are all with continuous cell ranges.

View Replies!   View Related
Contiguous Vs. Noncontiguous Cells Used In A Function Call From VBA
I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example:

Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function.

The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.

View Replies!   View Related
Copy Cells Where Range Address Resides In Cells Of Another Sheet
Tried to write a Macro to Range Cells in a sheet, setting the range values from a another cell. I have encounted a Run-Time error as select method of Range class failed. Below is the Macro.

Sub Macro1()
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("J4")
Set r2 = Range("K4")
Set myMultiAreaRange = Union(r1, r2)
Range(r1, r2).Select
End Sub

Have the Ranges been set incorrectly

View Replies!   View Related
Select Non Contiguous Ranges Via Macro, Using Variables
trying to select multiple ranges of data at once using variables as my selection range criteria.

I.e., I want something similar to:


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 Replies!   View Related
Copy Range If Cells Have Same Values As Another Range
1. I have two workbooks (eg. workbook1 and workbook2)
2. I compare the cell values in workbook1.sheet1.cell range (d6:d20) and workbook1.sheet2.cell range (d6:d20).
3. If the values in the range of cells are same, I want to take the value in workbook1.sheet2.cell range (d6:d20) and copy to workbook2.sheet1.cell range (d6:d20).

View Replies!   View Related
Copy Range Of Cells Over A Filtered Range
I wonder - is there a way to copy the green range over the filtered cells in col. C !? (I want to Copy range D20:D23 on to cells: C5, C9, C13, C17). I tried to select "Visible Cells Only" as the target for Pasting onto (using [F5] etc...) but no success. I prefer a solution that does not involve VBA. *** see attached picture.

View Replies!   View Related
Copy Cells And Range Of Cells To New Worksheet
I have an excel workbook that I have created to use in the creation of purchase orders. I have a spot for the vendor in cell D10 and my items are in the range of B17 to N17.

What I would like to have is a macro that I can assign to a button that does the following.

I have a worksheet called "Purchase Order" that has all the information in it.

When you click on a button named "Process", it will take everything entered into B17 - N28 and copy it to a worksheet named "PO# History" starting in cell B2 and moving down. Each time I click on "Process" I need the information in the range above to get entered into the next empty line on the "PO# History" worksheet.

This way I can maintain a list of items purchased and then play with them in a pivot table/graph and such.

Also, What I need it to do also is when process is clicked, the macro needs to pull the vendor name from cell D10 and put it in column A on "PO# History" for each row it adds from B17-N28.

View Replies!   View Related
Copy Range Without Cells' Name
is it possible to copy the range without naming the cells? The active cell is I29

The range I want to copy is A1:I28

I want to copy from 8 columns to left and 28 rows up to a cell above the active cell so really it copy range A1:I28

The reason for this is I'm creating a vehicle maintenance program for a mechanic and when he finished the first template, he can copy the entire template (A1:I28) to the next row down for the next maintenance inspection. And so he can continue copying the template one after the other for another vehicle maintenance.

View Replies!   View Related
Copy Cells From Range
I need to run down a column of numbers in col K. For each row over 28, I need to copy cells A to K in that row and paste to a new worksheet.

View Replies!   View Related
Copy Only Used Cells From Range
part of a macro I am working on is to narrow down the data from one sheet copy 2 columns and paste it into another sheet, now the issue i am having (workable) is that when i set a range (s2:t5000) to copy it copies all 5000 rows even though 2000 may not have any info in them, as i repeat this process it makes the database a bit messy. is there a way for me to make it only select rows with data?

here is a example of a part of the macro i am working on, keep in mind this will repeat with different criteria

Sub test()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With

View Replies!   View Related
If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?

2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?

View Replies!   View Related
Copyright 2005-08, All rights reserved