Set Discontinuous Range Of Columns As CopyRange Using VBA

Jan 28, 2014

I have this code to set a copyrange, which is throwing up a 'Wrong number of arguments' error

[Code] .....

Correct syntax for the code?

View 2 Replies


ADVERTISEMENT

Selecting Multiple Discontinuous Columns With Two Lines Titles

Jan 24, 2014

I have quite a long sequence of column for which the headers are all two-lines titles (with a line break). I need to copy many columns that are discontinuous, and the operation would save me a lot of time if I could select them at once. Of course, I know about selecting the columns holding CTRL, but here is the snag: as soon as a two-line title column is selected, the formula bar displays the content of the header, obviously on two lines, and I can't select any more columns. I tried many things, but nothing could solve this...

Is there anything I can do besides copying the columns one by one (which would take a long time) or renaming them?

View 1 Replies View Related

Selecting A Discontinuous Range

Sep 22, 2006

I'm trying to select a discontinous range, lets say B2:B30 & D2:D30 and this works fine using the code below:

Range("B2:B30,D2:D30").Select

However if I use the code below, B2:D30 is selected including column C.

Dim LastRow As Integer
LastRow = Range("a65536").End(xlUp).row
Range("B2:B" & LastRow, "D2:D" & LastRow).Select

View 2 Replies View Related

MaxIf Across Discontinuous Range - VBA Or Formula

Sep 6, 2013

I want to create VBA code, or a formula that achieves MaxIf across a Discontinuous Range.

I already have the basic Max If array formula which works in another place:

{=MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))}

Where each named range is a column of 20 continuous cells.

This actually sits within a larger formula {=IF(MAX(IF(EditAsset_MaintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))=0,EditAsset_DatePurchased+(EditAsset_MaintenanceCycle*30.4167),MAX(IF(EditAsset_M aintenanceType="Scheduled Maintenance",EditAsset_MaintenanceDateUndertaken))+(EditAsset_MaintenanceCycle*30.4167))}

Now I need to achieve a similar thing, but in a master list of all the data which necessitates a different layout so the data can't be in 2 columns, and can't be continuous. So:

Max the dates in cell BE7, BI7, BM7, BQ7, BU7, BY7, CC7, CG7, CK7, CO7, CS7, CW7, DA7, DE7, DI7, DM7, DQ7, DU7, DY7, EC7...

...IF the value of the corresponding cell two columns to the left of each is "Scheduled Maintenance",

e.g. cells BC7, BG7, BK7, BO7, BS7, BW7, CA7, CE7, CI7, CM7, CQ7, CU7, CY7, DC7, DG7, DK7, DO7, DS7, DW7, EA7

I tried creating 2 named ranges (one for the cells in each bullet point above) and using that in the array formula, but that didn't work and as this formula needs to go down 8,000 rows it would require 16,000 named ranges which isn't really practical.

Ideally I think I would perform this calculation in VBA and just output the answer to the relevant cell. [The VBA code will be triggered via a Worksheet_Change event when one of the date cells, or the 'maintenance cycle' cell elsewhere is changed].

I think this could be done by taking the values from the cells above into two arrays, and then 'max if' those arrays within VB somehow - but I don't know how to create the arrays or then perform 'max if' within VBA.

Alternatively a formula would suffice - I could insert this formula into the output cell via the VBA Worksheet_Change event when an input cell (date or maintenance cycle) is changed, and then have the same VBA code copy this cell and paste the value - can't have up to 8,000 array formulas live in my sheet for performance reasons! But I don't think this will be easily possible via a formula and VBA would be cleaner.

View 7 Replies View Related

Discontinuous Time Series Charts

Apr 21, 2008

I just joined ozgrid and this is my very first post. I have read the rules but spare me if I make some errors.

My question is related to plotting time series charts using VBA. I have data in the following format.

04/21/08 9:04:47 4.5055
04/21/08 9:04:48 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:49 4.5055
04/21/08 9:04:50 4.5055

04/21/08 10:05:00 3.5055
04/21/08 10:05:32 3.5055
04/21/08 10:05:40 3.5055
04/21/08 10:05:48 3.5055
04/21/08 10:05:51 3.5055

04/21/08 11:07:59 5.5055
04/21/08 11:09:00 5.5055
04/21/08 11:10:01 5.5055
04/21/08 11:11:02 5.5055
04/21/08 11:13:08 5.5055
04/21/08 11:14:11 5.5055
04/21/08 11:15:16 5.5055

The column on the left is NOW() output and the one on the right is data corresponding to the given date and time. You may interpret the above data as snapshots taken at regular time intervals.

Now what I want to do is make a chart with the above data. Where ever there is a break in the snapshots, represented by a blank line, I want the graph to jump to the next data point much as the way stock prices move from the closing price to the opening price on the next day. I am not able to figure how to do that. Since I want the x-axis to represent time, using scatter charts does not work since the intervals are not proportional to the time then.

View 9 Replies View Related

Setting Variable, Discontinuous Print Area In A Macro

May 29, 2009

I need to write a macro to set the print area to the first two columns (A & B), and the last 12 columns (the last column may change). In both cases I need to print all rows (start is row 1, last row is variable). I am trying to achieve something like the "Freeze Pane" effect with the printer. The first two rows contain column headers (dates). The first two columns contain information that needs to be included on the printout, whilst the last 12 columns contain the most recent data.
Various cells withing the selected ranges may be blank, but no row or column will be entirely blank.

View 2 Replies View Related

Auto Copy From Upper Cells In Discontinuous Data In Column

Apr 9, 2013

I have a column with data but some rows are empty, i want the empty rows to be filled with exact data from the upper cell so that no row remains empty. How can this be achieved?

View 4 Replies View Related

Excel 2003 :: Auto-populate Columns From Range 1 And Continue With Range 2

Jan 27, 2013

I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.

Here's what I have as start info and where I want to get to.

Sheet1
A
B

1
Ranges

[Code] ......

Excel 2003

View 2 Replies View Related

Remove Empty Rows Based On Range Of Columns If Columns Are All Empty (no Data) Delete

Oct 24, 2012

Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.

VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range

[Code].....

View 1 Replies View Related

Sum Columns Within A Range?

Aug 7, 2012

I'm trying to find an easier way of summing alternate columns without having to pick each cell out individually.

i.e. the example table below I could easily do A2+C2 and B2+D2 - in reality my table is much wider so I'd rather not have to go along and add up 50+ columns.

ABABSum of ASum of B
1234
2141
1222
3112

View 2 Replies View Related

Name A Dynamic Range With 2 Columns

Jun 28, 2014

I have data in 2 columns on which I need to do vlookup. This range for vlookup increases. I want to name this dynamic range. How do I do it?

View 1 Replies View Related

Sum Range Of Columns Until Criteria Met

Jul 28, 2014

I am searching for a formula that will evaluate a criteria that would be entered into a cell. I then need the total from the columns up to the point the criteria is met.

A simple example below. The criteria is in (what would be B1). Since that criteria was entered then the sum of the values in columns B, C, & D would be totaled. Excluding column E, or 516. If the value was changed to July-14, then 516 would be included in the sum.

PeriodJun-14

Apr-14May-14Jun-14Jul-14Total
XYZ 506 509 512 516 1,527

View 3 Replies View Related

Columns With Data In Range

Jul 24, 2008

If I have a range of cells, say B1:BA50, how can I find the number of columns that have data entered?

For example, if cells B1, E2, E20, K25, R15, R33, R45, T3, Z44, AA20, AZ16, AZ22 all had data entered, I'd have 8 columns with data.

How can I find the value 8?

This is what I have tried but doesn't work:

View 9 Replies View Related

Dynamic Range With 2 Columns

Nov 11, 2009

I'm trying to name a dynamic range for A2:B10000.... I know how to use the OFFSET function but was wondering to do it using the INDEX function

Both column will always have the same number of used rows.

Another question:

If I have dates in Column A, and values in Column B....what formula do I need to get me the last value (last row,most recent Date)

View 2 Replies View Related

Non Adjacent Columns In A Range

Nov 5, 2013

I am trying to use defined range names in a vlookup function. The target ranges contain non-adjacent columns. The formula wizard gives me an error msg "#value" and the vlookup returns #N/A.

View 2 Replies View Related

Compare Columns Within A Range

Sep 22, 2008

i'm trying to do some analyzing on an excel sheet and was seeing if there was a function that I could use to speed this up.

So in the file...
I want to Group what's in Column A So the 1's together and the 2's together... Once that is selected I want to know what the largest number in Column C is...

So if the function can select 1 in column A then tell me that 10 is the biggest number in Column C....

View 14 Replies View Related

Can Range Be Mixed Columns

Jan 25, 2013

For example if you want C23:C99 and column H23:H99 as well

Meaning a For each loop would display contents of

C23
H23
C24
H24 etc.

View 8 Replies View Related

Named Range -- Different Columns

Jan 28, 2009

I have a countif formula on another tab collecting data from these cells. I want to define a combined name DecBestFL with cells A1:A14 and G1:G14. Can I do that in Excel 2007?

On the other tab I would use =countif(DecBestFL,G5) G5 = "Y"

View 9 Replies View Related

Select Range Of Columns

Jun 26, 2009

I'm in the midst of preparing a Year-To-Date (YTD) simple financial report in MS Excel.

Firstly:
I allow user to enter a number in cell A2.

Secondly:
Cells from A4 - L4 are linked from Sheet1. Cell M4 will give sum of A4:L4. Users are not allowed to change data in cells from A4 - L4. Users are only required to enter a number between 1 to 12 in cell A2. Upon update cell A2, the VBA shall sum the number of columns as entered in cell A2.

For example, if user enter 3 in cell A2 then cell M4 must give sum of A4:C4. If user enter 8 in cell A2 then cell M4 must give sum of A4:H4.

View 9 Replies View Related

Delete Range Between Last Used Row Across 2 Columns

Oct 10, 2007

In a sheet where the longest column is A, I want to delete the rows between the last value in Column A and the last value in Column C.

View 9 Replies View Related

Hiding Empty Columns In Range

Jul 3, 2014

I am trying to hide columns in a range, "P8:ET1087" but it isn't working. After I autofilter a value, every row will be hidden except for the rows where the value is found. This is always 6 rows, won't be more or less.

The 6 cells in every column are the same and contain from 1 to 6:
Text
Text
Date
Number
Text
Date

What I am trying to do is to hide the column if all cells in that column are blank/empty after it's autofiltered. That for the 135 columns, from P to ET.

I was messing around with the following code:

[Code] .....

But it doesn't seem to work.

View 4 Replies View Related

Use Sumifs On A Range With Multiple Columns?

Aug 13, 2014

I set my range as follows :

[Code]....

This gives me two ranges with multiple columns of data, which should work fine.

Then try to gather my data as follows :

[Code] ........

But this DOESN'T work - it seems like SumIfs doesn't work with a range with multiple columns... ?

The code DOES work if the rng2013/rng2014 ranges have only one column of data instead of more...

View 2 Replies View Related

Range With Multiple Rows And Columns?

Mar 6, 2014

I am using the following range to copy data from Column A (from rows 3 on the the last row). How can I include Columns B and C into this range also, but still using Column A to find the last row?

[Code] .....

View 9 Replies View Related

Read Only Visible Columns From A Range

Jun 17, 2009

I do hav a sheet in which i do hav some column;s n data

Worktype
Metrics
April
May
June

in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.

View 4 Replies View Related

Maintain Range When Inserting Columns

Sep 30, 2009

I have a macro recorded that inserts into column E in the attached report, and then retrieves data from another worksheet in the workbook. In column D I am trying to keep a 6 month average which includes the latest inserted data, however every time I insert a column the relationship with column D is moved out by one. e.g. range E8:J8 becomes F8:K8.

I am also stuck with setting up a macro to import a new model into the report. I have set up a worksheet called new model with the manufacturer, model and fail descriptions, however all macros I have recorded fail when trying to insert the manufacturer and model into mulitple cells.

View 9 Replies View Related

Move Range Selection By Two Columns

Nov 19, 2009

I have a spreadsheet with data organized into columns in sets of two - the first column is an X value (Pixel number) and the second column is the Y value (pixel density). I'm trying to make a macro that will select the first set of columns, chart it, move the chart to the next sheet, then select the next two columns of data, chart them and move the chart to the next sheet etc. I've gotten as far as having it make the chart and move it, but I can't seem to make it select the next set of columns - I've been trying different things for a couple days now.
Code:

View 4 Replies View Related

Delete Columns With No Values In Range?

Oct 10, 2011

In .Range("A8:CJ8") I want code to delete all columns within that Range that do not have any values in the cells.

View 9 Replies View Related

VLOOKUP Where LOOKUP Range Is Two Columns?

Oct 27, 2011

I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.

My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:

=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)

View 9 Replies View Related

List As Columns Based On Range?

Mar 29, 2012

I am trying to create a data wall without using filters and manual drag drop

I have a sheet with 1000 sets of data such as below in 2 columns Name and Score

Name score
Fred 101
Barney 104
Wilma 110
Betty 94
Dino 96
Pebbles 112
Bam Bam 120

What I would like to do is sort the data into another worksheet with score as rows in a range and an automated way to list the names under the columns as names such as below (each range is a different) (not displaying correctly below bu names are stacked under the score ranges they correlate to)

95 - 99 100 - 104 105-109 110-114 115-119 120
Betty Fred Wilma Bam Bam
Dino Barney Pebbles

Is there an easy way to do this using formulae or other? Tried pivot table and it got a bit messy with the only way of doing it being names and data as columns with multiple repeated data.

View 2 Replies View Related

VBA Match Two Columns With Range On Another Sheet

May 3, 2012

I have a sheet (sheet2) with up to 60000 rows of text. I want to compare column A and B on sheet2 to a range of text (B1:B50) on sheet1. If column A or B on sheet2 doesn't match any of the range of text on sheet1, I want to hide the row on sheet2.

I'm currently using VBA to import sheet2, but haven't gotten really anywhere with hiding the non matching rows.

View 6 Replies View Related







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