# Macro: Copy Non-Contiguous Range Of Cells

Aug 24, 2008I 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?

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?

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

If I select a group of non-contiguous cells with control shift is there any way that I can shift the same selection over to the next column. For example, I have A4 and A8 selected. When I press shift and right arrow it doesn't work.

View 4 Replies View RelatedI 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 4 Replies View RelatedI 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 3 Replies View RelatedI want to paste a group of non-contiguous cells, and paste the values elsewhere on the sheet while maintaining the same relative position. is this possible via macro somehow?

for example, in column A i want to copy A1,A2,A5,A9; and paste into D1,D2,D5,D9 in one step.

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.

I have a non-contiguous range of cells that contains Values, Formulas, Text or Dates. In some cases a field containing a value could instead contain a formula depending on who is filling out the form and/or what information is known. For example, a cell might contain a value, i.e. Utilities, and I might either know the total ($76,310), or I might need to perform a calculation right in the cell to obtain the total (=54236+9587+12487).

My goal is to be able to copy the information contained in this non-contiguous range to the right some 52 columns (this is a safe temporary storage location) and then be able to copy it back should I need to.

One other problem may be that the source cell might be a merged cell.

Since I cannot determine whether the information is going to be a value or a formula, and I have some text and date formats to copy as well, is there some way to copy this range and keep the data regardless of format?

My Range might look like: (F4, C5, D6:D7, D9, D13:D15, D17:D18, D22:D25, D27:D30)

F4 is a date field, C5 is a merged cell holding text, D6 is the Utilities field described above. The other fields are all formatted as dollars or percentages.

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 RelatedIs 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 8 Replies View RelatedThe 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()

Range("g1:g8").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

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 RelatedI 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?

I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.

I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.

If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.

I have a worksheet with 35,136 rows of data. I want to calculate the average of every 4th cell in the same column. Is there a way to create a macro to select every 4th cell in the average formula? If so, can I then substitute min and max for average?

View 1 Replies View RelatedI am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter

=--(AND($AX8>$AA$4,$Y8>0))

and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

I have trying to develop the following macro (see below) which bases adds a column on another worksheet based on a cell value, this part works. I then want to copy the formulas from the column to the left of the inserted column, and this where I am struggling.

I found a question posed by 'TBW MK' and tried to adapt that. I can't work out how to express which needs to be copied - currently shown as Range(), given that it is a variable

Sub New_World5()

Sheets("Process").Activate

Dim ColNo As Integer

ColNo = Worksheets("Input").Range("H2").Value

With Sheets("Process")

[Code] .......

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.

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.

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 RelatedRange:

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.

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.

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.

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).

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

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.

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.

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

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

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,"

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 RelatedI 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?)

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.

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 RelatedIs 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 RelatedConsider 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

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.

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

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)")

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...

How to make the below selected range, which represents column Q, also include column A?

Range(ActiveCell, ActiveCell.End(xlDown)).Select

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.

ADVERTISEMENT