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
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?
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"
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"
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.
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.
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?
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.
If IsEmpty(Range("a1")) Then
Range("g2") = "A1 empty"
If IsEmpty(Range("b1")) Then
Range("g3") = "B1 empty"
If IsEmpty(Range("c1")) Then
Range("g4") = "C1 empty"
If IsEmpty(Range("d1")) Then
Range("g5") = "D1 empty"
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
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.
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.
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
MsgBox "No records returned.", vbCritical
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
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).
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 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
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?)
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)
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.
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,"
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
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?
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?
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" :-)
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.
Reference Non-Contiguous Named Range In Function
I have a split named range covering non-contiguous areas, ie:
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.
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.
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.
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.
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: ...
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
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
If Not Intersect(Target, Range("T:T")) Is Nothing Then
GlngRow = Target.Row
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..........
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?
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?
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.
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.
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.
Dim r1 As Range, r2 As Range, myMultiAreaRange As Range
Set r1 = Range("J4")
Set r2 = Range("K4")
Set myMultiAreaRange = Union(r1, r2)
Have the Ranges been set incorrectly
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.
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).
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.
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.
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.
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
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
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?