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 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 Replies View RelatedI 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.
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].....
I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
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] .....
I have named ranges and I want to get the total number of columns/rows or the last cell so I can index relative off off it to get at a value of an individual cell. No I dont want to go definning all names for this specific location, I want the flexibility to relative address it from the row/column or last cell of the named range. I looked at the column and columns props. The first gives a number that makes no sense and not a total of all columns and the last gives back a Range of columns, wonderfully recursive.
View 7 Replies View RelatedI would like to modify my code below to count columns and rows from a different range. Currently, the code below only reads from A1 of my data table. My data table starts at F7 with n columns and y rows. Each time I run the macro it does not recognise the range but only from A1.
How to modify this code to read for a defined range?
VB:
Dim iColumnCount As Integer
Dim lRowCount As Long
Dim iCol As Integer
Dim dAverage As Double
[Code] ......
I have a Sheet which gathers data from another Sheet in the same Workbook, then uses that data to produce more output. However at it's smallest it is only 2 rows of data deep and 2 columns of data wide, and at it's greatest it is 100 rows deep by 48 columns wide.
I would like to display only the relevant output by "Unhiding" the completed rows and columns.
After headings etc the rows start at 28 and the columns at N.
I have written the following which works for the rows but nothing happens with the columns,
Private Sub Worksheet_Activate()
Dim MyRange As Range
Dim MyRow As Integer
Set MyRange = Range("a28")
MyRange.Select
Do
MyRow = MyRange.Row
If MyRange.Value > 0 Then
Rows(MyRow).Select
Selection.EntireRow.Hidden = False
End If
Set MyRange = MyRange.Offset(1, 0)
Loop Until MyRow = 128
End Sub...
I posted this on Mr Excel, but did not get an answer. I hope someone here can help.
I have a rather large financial worksheet that I am trying to extract some specific data from.
The sheet contains budget and expense #'s for the fiscal year for several projects. The project names are listed in column G and the same project can be on multiple lines, depending on what the expense is. Column J contains either "Cap" for Capital, or "Exp" for Expense. The monthly data starts at Row L. Each month has 6 columns, the one that matters for this scenereo is the 5th one for each month, which is the Forecast Column.
Our Fiscal Year is April 1 - March 31, but this sheet ruins from March - March so that we can carry over anything from the previous year. So the forecast column for March is column "P", April is column "V" and so on, adding 6 columns for each month.......
With me so far?
What I am trying to do is get the total forecasted expenses amount for a specific project for the remainder of the fiscal year.
In other words I need to look for a specific Project Code in Row G, look for "Exp" in Row J, Then get only the numbers that are in the Forecasted Column for months in the future and add them all up.
Just in case that is not totaly clear----
criteria to meet
Column G = "Project Code"
Column J = "Exp"
Row 2 > this month
Row 3 = Forecast.......................
I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.
What I've managed to find so far on the forums is
Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select
which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that?
I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
I want to fill an array from values in range A1:H10. I want to fill the array with all rows in range and only columns B,C and E. I have the code below so far using index function.
Is there a more direct way to select all rows from desired range to avoid the need to create an array of rows from 1 to LastRow and then use Application.Transpose(RowsArr) (in red) inside Index()?
I'm attempting to match a name with the row and column numbers of data that is laid out in a grid format. It looks something like this:
Denson
Matthews
Smith
[Code]....
This works just fine, except for the fact that once in awhile the same name will appear in the grid twice, and the row and column returned will be off (it will return the row for one of the Smiths, for example, and the column for the other).
I know why this is occurring so I'm looking for a better way to do this. The good part is that I don't necessarily need both locations returned for each "Smith" ; I just need the correct row and column for at least one of the instances. I'm looking for a better way to do this.
How 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.
I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.
Pseudo code (how I'd like the syntax to be, but isn't):
Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With
I created a macro on an Excel spreadsheet:
Sub Macro1()
Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57
End Sub
When the macro is run, it works with the exact amount of data that I've entered.
However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)
How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?
I'm using a template that has formulas using a $ sign to attempt to stop the ranges they refer to changing. The problem is, when I use the template (which involes Access importing some data and adding columns to the sheet the formula refers to in the process) the rows referred to in the formula change in line with the number of rows of data that have been imported. Only the row numbers change not the column headings. So for example:
=( SUMPRODUCT(((Workings!$H$3:$H$1000=Explanation!B9)+(Workings!$E$3:$E$1000="Buy")*(Workings!$J$3:$J$1000))))-998
becomes
=(SUMPRODUCT(((Workings!$H$511:$H$1508=Explanation!B10)+(Workings!$E$511:$E$1508="Buy")*(Workings!$J$511:$J$1508))))-998
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View Related