Range - Dynamic Use #rows & Cols Vary

May 29, 2007

i 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

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.

View 9 Replies


Format A Range Of Cells, Columns And Rows Will Vary In Length

Jun 16, 2009

I created a macro on an Excel spreadsheet:

Sub Macro1()

Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Selection.NumberFormat = "0.00%"
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
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?

View 9 Replies View Related

Dynamic Sum Function (number Of Sheets Can Vary From Time To Time)

Sep 8, 2009

I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.

Any idea about a dynamic sum function that will do this, in combination with VBA?

View 2 Replies View Related

Finding Blank Cells In Any Row Across A Range Of Cols

Jan 9, 2007

My excel file was imported from a 3rd party and has many blank rows across the entire sheet.
I want to delete these rows if every cell across is blank.
How is this accomplished?

I tried the formula "=isblank(C2:C18)" however I get a "False response on those rows that are completly blank.

View 9 Replies View Related

Excel 2007 :: Macro - Copying Column In Which Number Of Rows Can Vary

Sep 23, 2013

I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.

The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?

Sub IPT()
' IPT Macro

[Code] .....

View 2 Replies View Related

Dynamic Range. Rows & Columns

Dec 7, 2006

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"

View 5 Replies View Related

Hide Rows In A Dynamic Range

Jan 17, 2007

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

View 2 Replies View Related

Selecting Dynamic Range Of Rows Macro?

Jan 28, 2014

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

View 10 Replies View Related

Dynamic Range Of Columns With Fixed Rows

Jan 5, 2009

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 Related

VBA To Remove Rows That Contain 0 Or Blank In Dynamic Range?

Jun 28, 2013

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 Related

Dynamic Range For Table With Unequal Used Rows In Column

Feb 19, 2008

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

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.

View 5 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

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?

First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

Value In Col 1 For First Non-zero Value In Cols 2-5?

Sep 3, 2012

I need to discern the first row where columns B or C or D or E become non-zero. This is because column A countains dates, and if I subtract one day from the date in the non-zero row I have the date of the analysis I'm examining.

The problem i have is that the code I've put together below gives me the date of the absolute last row in the spreadsheet, "31/12/2015 23:30:00" from row 70129 which isn't that useful. I'm expecting a date in August just gone which is on row 11618.


Sub test()
Dim lngLastRowU1 As Long
Dim lngLastRowU2 As Long
Dim lngLastRowU3 As Long


View 1 Replies View Related

Hiding Cols Using A Macro

Oct 21, 2008

I use the formula below to hide rows on the current sheet using the value in cw104 on sheet "POINTS"as the upper limit & cw105 as the lower limit.

Rows(Sheets("POINTS").Range("cw104") & ":" & Sheets("POINTS").Range("cw105")).EntireRow.Hidden

i want to do the same thing with the formula below, but for columns (i want to use the value in cell cw106 on sheet "POINTS" as the upper limit & cw107 as the lower limit.

Columns("aE:aF").EntireColumn.Hidden = True

i tired it like this and it wouldnt work
Columns(Sheets("POINTS").Range("cw104") & ":" & Sheets("POINTS").Range("cw105")).EntireColumn.Hidden

can the col be numerical values and not letters?

View 9 Replies View Related

Filter And Copy Selected Cols To Another W/s And Add Equations

Aug 22, 2009

I need to automate the attached as per the following (by not recording macro unfortunately).

Sheet one has a dump of data. First, I need to change the data format from yyyymmdd to dd/mm/yyyy. Then I need to filter for region EU and only copy the filtered data from columns A, C-D, F and the difference from columns I-J to Sheet 2. Then in Sheet 2, I need another column to calculate the difference per unit.

View 9 Replies View Related

Error 1004 When Trying To Move Cells 24 Cols To The Right

Jun 11, 2008


From the above thread I am using the following macro...

Sub MoveOver()
Dim MoveRange As Range

Application.ScreenUpdating = False
With Range("B1", Range("B" & Rows.Count).End(xlUp).Offset(, 21))
.AutoFilter field:=1, Criteria1:="=C&P"
On Error Resume Next
Set MoveRange = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With
If Not MoveRange Is Nothing Then
MoveRange.Insert Shift:=xlToRight
End If
Application.ScreenUpdating = True
End Sub

I am getting an error 1004 message saying cells cannot be moved off of the spreadsheet. However, my last cell on the sheet is J28956.

Columns B through J contain data. Columns beyond J do not contain data.

Can someone give advice on getting the macro to work.

View 9 Replies View Related

Summing Values From Array W/ Multiple Conditions-cols

Oct 1, 2007

I know there have been many posts about multiple conditions in arrays, but I didn't find one that applied to my situation, so I'm hoping can help me out of jam. I'm using Excel 2000 on XP.

I have a database of sales information. Customer name is listed in column C, and columns F through Q contain the sales for the months of July - June (fiscal calendar year), with the headers for the months in row 4. There can be many rows of sales for the same customer, hence the need to sum them.


customer1 2 3 4
customer1 2 3 4
customer2 2 3 4
customer2 2 3 4

The problem is that this information will need to be updated every month to reflect sales year-to-date, meaning that the conditions for summing the data will change depending on how far into the year we are. So for instance, in August I will need to sum all the records for customer1 in the July column (column F) and the August column (column G), but next month it will have to sum the records for customer1 from July, August, and September (col H).

The result of this information would be displayed on a different worksheet. My thought was to assign a range of 12 cells (Sheet2!A1:A12) and to populate those cells with any of the months that need to be summed thus far. So for example, through September, A1 = "July", A2 = "August", and A3 = "September". Then the formula would use those values to evaulate the conditions. So in plain English the formula would:

sum the values in columns F : Q where the monthly header = the values in the range Sheet2!A1:A12 for all records where CUSTOMER = CustomerName

View 9 Replies View Related

Chk Valus In 3 Cols If Lengh Of Charecter Is Met Or Move To Next Sheet

May 26, 2009

I have 40000 rows of data to check. I always filter Column E by Suppplier Name, like supplier Alpha or Bravo,Charlie,Donkey, or Elephant,France,Goal etc.

What I manually check is I filter the column E by Alpha, then I will get some 5000 rows of data of Alpha, I will look if all the rows in Column B contais "-3digit" numbers like -123,-548 etc if not I will assume its incorrect and copy the entire row and put it in next sheet.

If Column B is right that is, if it contains -3digit then I will again look in Column F ( Invoice Numbers) the invoice number should always be 5digt-4digit all numbers without space or any special charactors except Hypen(-). It may have 5digt-4digit-5digit-4digit etc, if any values doesnt match with the said criteria i will again assume it as incorrect and move to the next sheet.

Now once this is done i will filter againn Column E by "Bravo" all the conditions are same, that is if the supplier name is Alpha Bravo, charlie, Donkey, or Elephant then i will do all the checks mentioned above or else i will ignore all other supplier....

View 9 Replies View Related

Get Text To The Right - Length Will Vary

Feb 8, 2011

If I have a cell with this info: [6126]BOB SMITH

What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]

View 8 Replies View Related

Vary Data Marker Size By Value?

Apr 17, 2014

Is it possible to vary the size of data markers (eg spots) in a chart by their value?

View 2 Replies View Related

Vary Columns Based On A Number

Feb 5, 2009

You can always add numbers together. however, how can you vary columns based on a number?

for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.

View 2 Replies View Related

Vary Source Data Via Combo Box

Feb 23, 2007

I'm trying to amend a chart via combo box. Cell C15 returns a dynamic named range, based on a vlookup.

My problem is in having the code pick up the value that C15 returns, rather than the formula in it.

Set rng = Sheets("Chart lookup").Range("C15")

ActiveChart.SetSourceData Source:=rng, _

View 7 Replies View Related

Macro To Sort And Align Employee Data In Multiple Cols To A Header Row

Jul 9, 2014

I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.

My Source excel file looks like:

ABC A-101 B - 202
DEF B-203 C - 100

The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.

The output I want is:

ABC 101 202
DEF 203 100

There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.

In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc

View 1 Replies View Related

Testing For Values That Vary Within Specific Tolerance?

Apr 22, 2014

I'm trying to identify where a set of values 'agree' within a specified tolerance.

I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.

To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.

I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.

Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.

View 2 Replies View Related

How To Make Universal List That Can Vary By Different Location

Mar 12, 2014

I am trying to use HLOOKUP to find a location in another sheet and display all the names in that specific location. Also can change location at will and will display the names for that location.

I have attached a spreadsheet that kinda has what I want. It does not have to be HLOOKUP but that is all I know, Hlookup and vlookup.

View 4 Replies View Related

Opening File Paths Where Filename Will Vary

Feb 4, 2014

I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.

Is it possible to write something like:

Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"

Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?

View 1 Replies View Related

Charts: Vary Colors By Point With Area Fill Effect

Oct 30, 2006

I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series

So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?

View 3 Replies View Related

Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

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

View 9 Replies View Related

Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

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

Dynamic Range For Pivot Table :: Error : Range Is Invalid

Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:


I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

View 3 Replies View Related

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