I am still trying to develop a macro that will copy and paste a set of formulas into a dynamic destination range. I have included a worksheet that shows what I am trying to do. Basically when using AutoFill I need the columns of the destination range to be dynamic based on the number of rows in another sheet.
View 13 RepliesHow do I define a " Range Name" that is fixed? So if I insert a row above the range it has no effect on the orginal range.
IE: Range Name "MyRange" = A10:A100
I insert a new row at A4 then "MyRange" = A11:A101
I want it to stay at A10:A100.
create a Dynamic Named Range for Pivot Tables
the pivote table takes data from A1 to Kx
x is the last row of column K that is not 'blank' ( whenever I insert a new row),
I already know how to define a zone but not know how to to make it "dynamic"
i have a unique problem. I have a spreadsheet with 2 worksheets.
Worksheet A
Dlr# Dlr Name Zip Code
X123 Dane's 50266
X123 Dane's 50266
X123 Dane's 53135
X983 Andy's 50254
X983 Andy's 50254
X348 Ryan's 45678
So, this spreadsheet has some duplicate rows and some unique ones. All are needed as duplicate zips mean that the dealer is sending out multiple mailings to the same zip code.
My task is to transfer these into Worksheet B so that duplicate zips go in different columns but unique zips don't. So this is what Worksheet B will look like:
Worksheet B
Dlr# Dlr Name Drop 1 Drop 2 Drop 3
X123 Dane's 50266 50266
X123 Dane's 53135
X983 Andy's 50254 50254
X348 Ryan's 45678
My spreadsheet has over 500 dealers with over 1500 rows of data in it, so if there is an automated way to create spreadsheet B.
Im using this formula in a spreedsheet =(B$3*0,5)+(B$3+(B$4*0,5))+(B$3+B$4+(B$5*0,5))+(B$3+B$4+B$5+(B$6*0,5))+(SUM(B$7:B$28)*4)
What i want is to be able to drag this down for multiple rows but i want the formula above to only change columns i.e.
Next row gets C$3 and so on.
I'm trying to produce a fixed format pivot table in Excel 2010. Normally I'd just construct a manual table using COUNTIFS, SUMIFS etc, howver, for this exercise the requirement is to be able to click on any field and have a tab pop up with the relavent data a la Pivot Table. But....I need the Pivot table to have a fixed format (which I can do with 'Preserve cell formatting on update') and to have all of the rows and columns in irrespective of whether there is data or not (i.e. if I haven't sold any apples in June, I still want the 'Apples' field to appear, just with a value of zero). I had assumed that the option I needed was 'Show items with no data on rows/columns' but these options are greyed out. I've tried right-clicking on the whole table, on individual fields and on labels but still get the same greyed out options.
Is there any way I can retain all of the rows and columns?
I have created a spreadsheet which is tallying up various data. I have a number of different categories e.g. A, B, C, D and within each one of these are sub categories. I then have a tally for the number of each category and within that the number of each sub category. I then want to sort the data so that first the main category is sorted in order of highest tally to lowest. the sub categories however need to stay with their primary category. I then need to sort each sub category within the main category.
the data looks omething like this:
category tally sub cat number
A 6 A 3
B 4
C 6
B 9 A 1
B 6
I'm working with a pie chart that has a dynamic range as its data source and a dynamic range as its legend. The chart shows ratios of bought items depending on customer type. So, when you switch from a "Hair" customer to a "Bath & Body" customer, the ratios shift and the legend changes as well (i.e. "Hair - 25%" to "Hair - 10%").
Is there a way to fix the colors permanently so that as the ranges change, the colors remains the same for each category?
I've seen some VBA for it, but it was for data in long columns, whereas my data is in one range in the middle of the worksheet.
I can attach the file if it makes things easier.
I have searched for this on the site as I thought it would have already been asked but I couldn't find anything. I have a formula in cell C7 which I wish to copy both down to the last row (xlup from column A) and across to the last column (xltoleft on row 6) but I cant seem to work out how to do it. I have used the .autofill before but for dynamic rows only. Is it possible to do for columns too?
View 2 Replies View RelatedI have a list of several hundred columns, beginning with column "G:G", with varying numbers of rows of data in column - each row dipicting a monthly data point. I'd like to average the numbers in each column (need to average over the appropraite time-frame) and compare that average with the corresponding average (same time-frame) for benchmark (column"F:F"). The problem is I don't have the same number of data points in each column; some have data points for every month for the past 33 years, and some just a few years; almost all have differing beginning and ending dates as well.
[Code] ....
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 RelatedI'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)
How would I modify this macro to handle a Dynamic Named Range In the example below A27 is the last row in my data set, but its last row number is dynamic.
I'm using this code just after finding the last row in the dataset, but found it breaks if the last row doesn't fall in the A27 range.
I find the last row prior to executing the TextToColumns code.
Range("A65536").End(xlUp).Select
Sub TextToColumns()
Selection.TextToColumns Destination:=Range("A27"), DataType:=xlFixedWidth, _
FieldInfo:= Array(Array(0, 1), Array(3, 1)), TrailingMinusNumbers:=True
End Sub
I need a macro to start at cell A11 and select across 10 columns and down to the Lastrow (last row with any data).
There are hidden columns after column D so it CANNOT have any columns hardcoded except for column A.
This selection then needs to be set as a single print area to print out on 1 page. I have already set Rows 1-10 as a title area to print at the top of each page.
Code i currently have is:
[Code] .......
I just need it to now go across 10 visible columns and set as a single print area.
I'm trying to hide all rows from row 3 to a variable row. I've struggled to simply select the rows that I want to hide. I have managed to select a range (containing the rows that I want to hide). I now need to select the rows contained in the range currently selected and then hide them. My code is as follows:
Sheets("Program").Activate
Activesheet.Range("B3").Select
Sheets("Program").Range("B3", ActiveCell.Offset(LastProgramRow - 10, 0)).Select
Selection.Row.EntireRow.Hidden = True
The last line of code fails. I have tried modifying it to variations of:
Sheets("Program").Row (3), ActiveCell.Offset(LastProgramRow - 10).Select
but this also fails. How can I change this code so that only the row is selected and not a column?
I am trying to select rows in excel using a macro and then hide the rows, but i want to be able to add rows within this selection and still be able to hide the complete selection of rows.
The macro i am currently using is shown below: This hides rows 131 - 205, but if i was to add in another row in between so the range i now want to hide is 131-206 can i get the macro to update to this new range?
[Code] ......
Im working on a macro that i need to delete any row that contains 0 or blank in a dynamic range that starts at H23.
View 1 Replies View Relatedi have data that will vary from item to item in the $ of rows associated
and the # of columns to be dealt with.
item1 Line 1
Line 2
Here i want to have a formula put and copied across X columns
item2 Line 1
Line 2 other data going across in the columns
Line 3
Line 4
Here i want to have a formula put and copied across X columns
item3 Line 1
item4 Line 1
get my meaning??
columns involved will be known before i get to this bit of code !!
as an example lets say it's 8 columns
using the record macro i get this bit of code
Range("E14:M14").Select
Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
This is fine but i don't know what row or the columns are before i run the macro so the Range
("e14:M14").select wont really work.
I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns
this is close to my idea in my mind conceptually
Range("C2:C" & LastRow)
but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.
How would I select a dynamic range in VBA without using name a range in Excel. I have searched for possible answers, however they use xldown, or xlup which works great for non-empty cells. My problem need to select the area where there are empty cells. Here is a snap shot of the data looks like:
Trade Date A B C D E
2008-Feb-11 450,432
2008-Feb-13
2008-Feb-15
The cell with number is where I got the formulas. Now i wish to fill that formula with the rest of the area. However, the problem is the number of rows and number of columns may change in the future. So I need to select those empty areas in a dynamic way.
Im new to excel and problem regarding the sumproduct function.
I have three columns in my sheet1, column A and column B contain 1000 numbers.
Hence, column A goes from A1 to A1000 and column B from B1 to B1000.
In the first row of column C, i want to use sumproduct on A1:A5 with B1:B5.
My problem arises when I want to drag the formula down, in the second row of column C I want
to use sumproduct on A6:A10 with B6:B10 but the formula uses A2:A6 with B2:B6.
Is there a way to fix the ranges when I am using sumprodct so it takes "fixed" ranges like every fifth,
A1:A5 with B1:B5 , A6:A10 with B6:B10, A11:A15 with B11:B15 and so on.
I have 5 columns with data in each
I want to create a 6th column that looks to the columns on the left with data in ti and concatenates all data in the 5 columns and puts it into one cell in the 6th column however put a space between each break of data so that it can be distinguished which bit of data was in what column previously.
The challenge is the new 6th column can only contain 30 characters - When it exceeds 30 characters then create a 7th column and put the rest of data in the 7th column, again the 7th column can only have 30 characters so if exceeds this then put the remaining characters in a 8th column
There will never be more than a total of 90 characters in the original 5 columns so there will only need to be scope for a maximum of 3 additional columns
So for example
Column A had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column B had two words in it that totaled 20 characters (the space between the two words is also counted as a character)
Column C had a word that contained 10 characters
Column D had a word that contained 5 characters
Column E had a word that contained 10 characters
Then the result would be
Column F would only have the data originally held in Column A (because it can't include Column B's data as this would exceed the 30 characters)
Column G would have data that was originally held in column B and column C - with a space between B and C data
Column H would have data that was originally held C, D and E - with a space between C, D and E data
Another point to consider is if in one of the orginal 5 columns had say 3 words in it and lets say the 3rd word is the word that exceeds the 30 character limit, then the whole of the third word is to be carried oved to the next new column, I can't have words cut in hlaf with one half in Column 'F' and the other half in Column 'H' for example.
I need the different categories to be separated into separate columns, but they do not appear to be separated by anything useful or a fixed width. Not only that, but the last few columns were moved onto a second line when I copied the data into Excel for some reason. The data originally came over in an Adobe Acrobat file. This is what the final product should look like: ....
View 9 Replies View RelatedI'm trying to autofill "vertically" in a spreadsheet using a formula which loads value from a different sheet.
Lets say I have cell (A1 for example) ='2013'!A39
Now I want to fill A2-A30 in a way that the formula updates to ='2013'!B39, ='2013'!C39, ='2013'!D39...and so on
I've tried using ='2013'!A$39, but this only copies the formula as it is. When I try to autofill cell A2-A30, the formula still is ='2013!A$39
Is this possible to do?
I'm getting random data and i want to put it in a fixed formatting
Therefor i need to be able to search a name and copy the corresponding rows to a fixed position
I put in a file
I want to count the number of rows from a fixed cell (eg. $A$2) to a "named cell" (eg. "Department Winn").
I'm struggling with getting my Excel worksheet to fit nicely on the pg for printing to .pdf. I could tinker with column widths manually to work this out for a printed report. But I need to print a couple hundred reports with varying column widths, so I need a vba solution.
The context is that I'm using vba to process two sets of interlinked data on a worksheet. I only want to print one set of data, and keep the second set out of the print view. To do this, I'm keeping the first set of data in columns 1 to 10 and the second set of data in columsn 21 to 30. I set the print range to only include columns 1 to 10.
This works fine for keeping the second set of data off the printed pg. The problem is that there is always a gap between the end of column 10 and the maximum width of the printed pg. Given the formatting of the report, this doesn't look very good.
I know that page setup offers the "fit to" option. This would work if I wanted to fit everything on the worksheet on the pg, but I don't. I just want to fit columns 1 to 10 to the pg. Is there a way to do this, allowing for varying widths in columns 1 to 10? Is there a way to do it via vba?
I have a long (190,000) list of customer data, all in Column A (unfortunately with blank rows among it, but working now to fix that).
Down the column, individual customers are bookended by a "adf" and a "/adf". (these have open and close brackets like HTML code, but I cannot reproduce them in this forum).
For each customer, I need to find the rows that begin with (brackets spelled out since I do not know how to show them):
1. [open bracket] vehicle status
2. name part=3D"first"[close bracket]
3. [open bracket]name part=3D"last"[close bracket]
4. [open bracket]email[close bracket]
5.[open bracket]phone time=3D
6.[open bracket] name part=3D
And transpose only those rows it into columns.There is a dynamic number of rows for each customer, so there's no way to simply count and transpose, as the columns would all be mis-entered.Somehow it needs to recognize those 6 row items, and transpose those values only, with the and the only telltale of a start and finish of a specific customer.
EDIT: How about a macro to delete all rows except those that contain those partial values above?
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column:
000122042009ABCDEFG00567
Required result:
0001|22042009|ABCDEFG|00567
when running below macro; result shows:
1|22042009|ABCDEFG|567
(Beginning zeros figures of the first and last column disappear)
Applied Macro:
In the attached file, i want to select the range from cell B8 (which is fixed), to used range i.e. till Cell F18 (but the problem is, cell F18 is not fixed). Last used row or column can change. I want to do it by using VBA.
Here is the sample file Select Range.xlsm
Range Macro - looking to create a simple macro to highlight a colum range from any location in column A that the cursor has landed on to the fixed location "A3". Example: If I am on A34, then the macro would highlight A34:A3. I can then edit the format in that range. Idealy, I could search on Column A first for a given text, then run the above macro to highlight every cell between that given text and "A3".
View 3 Replies View Related