Set Discontinuous Range Of Columns As CopyRange Using VBA
Jan 28, 2014I have this code to set a copyrange, which is throwing up a 'Wrong number of arguments' error
[Code] .....
Correct syntax for the code?
ADVERTISEMENT
Selecting Multiple Discontinuous Columns With Two Lines Titles
Jan 24, 2014I 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?
Selecting A Discontinuous Range
Sep 22, 2006I'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
MaxIf Across Discontinuous Range - VBA Or Formula
Sep 6, 2013I 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.
Discontinuous Time Series Charts
Apr 21, 2008I 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.
Setting Variable, Discontinuous Print Area In A Macro
May 29, 2009I 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.
Auto Copy From Upper Cells In Discontinuous Data In Column
Apr 9, 2013I 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 RelatedExcel 2003 :: Auto-populate Columns From Range 1 And Continue With Range 2
Jan 27, 2013I 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
Remove Empty Rows Based On Range Of Columns If Columns Are All Empty (no Data) Delete
Oct 24, 2012Using 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].....
Sum Columns Within A Range?
Aug 7, 2012I'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
Name A Dynamic Range With 2 Columns
Jun 28, 2014I 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 RelatedSum Range Of Columns Until Criteria Met
Jul 28, 2014I 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
Columns With Data In Range
Jul 24, 2008If 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:
Dynamic Range With 2 Columns
Nov 11, 2009I'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)
Non Adjacent Columns In A Range
Nov 5, 2013I 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 RelatedCompare Columns Within A Range
Sep 22, 2008i'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....
Can Range Be Mixed Columns
Jan 25, 2013For 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.
Named Range -- Different Columns
Jan 28, 2009I 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"
Select Range Of Columns
Jun 26, 2009I'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.
Delete Range Between Last Used Row Across 2 Columns
Oct 10, 2007In 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.
Hiding Empty Columns In Range
Jul 3, 2014I 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.
Use Sumifs On A Range With Multiple Columns?
Aug 13, 2014I 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...
Range With Multiple Rows And Columns?
Mar 6, 2014I 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] .....
Read Only Visible Columns From A Range
Jun 17, 2009I 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.
Maintain Range When Inserting Columns
Sep 30, 2009I 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.
Move Range Selection By Two Columns
Nov 19, 2009I 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:
Delete Columns With No Values In Range?
Oct 10, 2011In .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 RelatedVLOOKUP Where LOOKUP Range Is Two Columns?
Oct 27, 2011I 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)
List As Columns Based On Range?
Mar 29, 2012I 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.
VBA Match Two Columns With Range On Another Sheet
May 3, 2012I 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.
