Automatically Define A Variable Range Using A Macro

Nov 25, 2009

1. I need to define a variable range of all the USED cells in a column (starting at row 2 as the row 1 is the header)

2. I need to do this for columns A through to P inclusively (all using the same lastrow reference as that of column A)

3. I need to call the range name by the contents of the header in that column (Row 1)

4. Some of the data in the header row (row 1) contains spaces - can we change this to an underscore for the range name only?

5. Does a range only work for the active worksheet? Because i will have multiple worksheets, all with the exact same information in row 1, but different numbers of rows (and different data as well), but i will need the range defined on each worksheet......

I've found the following code and thought it was it, but it only defines on 1 worksheet, and for some reason only defines ranges for columns 1 and 2. Also it defines the range as A1:A65535 - not the USED range.....

View 9 Replies


Define Range As Variable?

Feb 23, 2013

why this won't work.


Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange

View 6 Replies View Related

Using Variable To Define End Of Range

May 19, 2008

how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:

Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete

I'd like to define the end of the range with more accuracy than I'm currently doing.

View 9 Replies View Related

Define A Variable Range

Dec 22, 2006

I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:


Sub CalculateATR()
If ActiveCell.Value > 0 Then
Exit Do
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0

End If
End Sub

View 8 Replies View Related

Define A Cell Range From Variable Values?

Jul 21, 2009

I have a range staring with cell "A5" and going to Column "K8+" that I need to copy. My issue is that the number of rows to copy will change. I have the number stored a variable "a" in my coding. how do I code this to work?

View 5 Replies View Related

Declare & Define Variable As Date Range

Nov 13, 2006

I'm fairly new to VBA and I need to define a variable as a date range
i.e. Period1 = 1Apr2006 to 29Apr2006, Period2= 30Apr2006 to 27May2006 etc

View 9 Replies View Related

Define Range During Macro

Apr 25, 2006

I'm trying to define a range to be copied during execution of a macro. The range to be copied must include all contiguous data from a defined starting range to the rightmost column and the bottommost row containing data (boht of which can change depending on input data). I use <End>+<RightArrow> and <End>+DownArrow> to define the range when working in Excel (not under macro control).

I used the macro recorder to define the code for the process described above, with results shown below:

Sub Macro2()
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
End Sub

When I run the macro, however, the End(xlToRight) and End(xlDown) commands do not have any effect (i.e., the rang is not made any bigger when these statements are executed).

I've used the same code in previous macros (with previous versions of Excel and Windows), and it worked fine. I'm running Excel 2003 under Windows XP Professional now.

View 5 Replies View Related

Define Named Range In VBA Macro

Dec 29, 2009

I am trying to define a number of ranges in different sheets using VBA I am using the follwoing syntax but I am getting a 1004 error:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

ThisWorkbook.names.Add Name:="Test", RefersTo:=Worksheets("Sheet1").Range("E6", Range("E6").End(xlDown))

End Sub

As long as I have sheet1 active, it works. If I have another sheet active and change smt, i get the error.

The code is located in the ThisWorkbook object.

View 6 Replies View Related

Macro: Re-Define Named Range

Nov 21, 2006

How can I redefine name of the cell? This is what I have tried and not work

intWDColumn = Range("rngWaterDepthStart").Column + 3
intWDColumn = intWDColumn + 1

strNameRange = "rawData!R7C3:R7C" & intWDColumn

ActiveWorkbook.Names.Add Name:="rngWaterDepthStart", RefersToR1C1:=strNameRange

and the cell name have define to ="rawData!R7C3:R7C7" which the "" stop the rename working I belive.? What I want is to add or reduce extra column into the name depends on macro.

View 4 Replies View Related

Define Dynamic Range In Macro Code

Feb 26, 2008

I have been tring to define a dynamice range in VBA. At the moment, I use following method to Define the range,

Sub OTC_Future_Total()

Dim DynamicRange As Range

Set DynamicRange = Range(Selection, Selection.End(xlDown))

View 5 Replies View Related

Define Name -variable- By Saving As

Feb 16, 2010

I am looking for a solution to define a name by saving as. I think I'm almost there, but can't find the part where i can name it.

In steps:

1) Define the path and the name of the current file as string - to delete extension (=.csv)
2) Save current file as current name with .xls in folder where is current file saved before.

This is a part of my code - the part that is neccesary to see what I am doing: ...

View 8 Replies View Related

Define A Index With The Use Of A Variable

Jun 8, 2006

i wrote the following statement that worked:

'Selection.FormulaArray = "=UserDefault!R[-27]C[-1]:R[-23]C[1]" '

but i need to define the index with a variable

'dim franz as integer

franz = 89

Selection.FormulaArray = "=UserDefault!R[franz]C[-1]:R[-23]C[1]" '

this does not work, is there a possibility to do this? the index from where te values should be read is calculated in the macro, so i can not use fixed values.

View 3 Replies View Related

Define Path & Filename As Variable

Feb 16, 2007

Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1

I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"

However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.

View 2 Replies View Related

Define Pivot Table Source As Variable

Dec 28, 2007

I am producing graphs that compare last years figures (static) with this years figures ( Dynamic) and this years figures come from a Pivot table and Worksheet that has been imported. The value returned in each cell for the graph's dataset is derived from a specific Pivot table row and the column name (month) that matches the column of the data set - so far so good. The formula currently looks like this.

=IF(ISNUMBER(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),0)

where 'Count PS' is the name of the worksheet and Q3 is the reference cell that holds the Month Name. The problem is that I will not always be creating the report and other users may use a different name for the imported worksheet. I want to be able to set the name of the worksheet to be referenced in a supporting worksheet so that the user could simply amend the cell value to be the name of the source worksheet, but I can't work out the syntax

View 2 Replies View Related

Automatically Define Print Area Before Printing

Aug 17, 2007

I've seen code to set the print area for a dynamic range. However, how can the print area be defined by these parameters:

A1 to one extra blank row below the last cell of data in column A (height), and
last column with data in row 12 (width).

Here is what I'm starting with...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet. Names.Add Name:="Print_Area", _
RefersTo:=Range(Range("A1"), _
Range("A1:IV65536"). Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious, after:=Range("A1")))
End Sub

I have copied formulas in other columns down beyond what the last row of col. A will be. Also, this will need to be applied to about 7 sheets in one book.

View 4 Replies View Related

Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?

Feb 19, 2010

I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.

The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?

View 6 Replies View Related

Macro - Variable Range

Oct 25, 2009

When I'm recording a macro it records the range I'm currently doing... example - whatever figure is in Column A I want to multiply by 2 - the result being in Column B. I have figures down to Row 10 in Column A, so the range is A2 - A10. (A1 being a header).

Is there a code I can put in the macro that doesn't set the range to only A2 - A10, and will multiply anything that's found in Column A - except for when the Cell is blank?

View 9 Replies View Related

Define Range() In Vba

Jan 23, 2007

I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?

View 2 Replies View Related

Select A Variable Range Macro

Aug 23, 2006

I have tried various syntaxes but nothing is working for me. I am using XL 2000 so it doesn't have the relative address function in the macro! So here is what I have

Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True

I need to be able to select from the originating cell which will always be in "T" Col thru to "CZ" col but will be on different rows.

View 2 Replies View Related

Define A Time Range

Jan 21, 2010

I have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.

The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.

View 8 Replies View Related

Define An Unknown Range

Aug 13, 2006

is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown

View 9 Replies View Related

Define Named Range

Feb 7, 2007

how to set up an array within a worksheet

View 3 Replies View Related

Define Range With Numbers Only

Mar 26, 2008

how do you go about referencing a cell by using a value of an integer? For example, I want to select cell A5. I have the number i= 5 (in my vba code) and I want to use that together with the row letter A.

View 4 Replies View Related

Select/process Variable Range With Macro

Dec 15, 2006

I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.

Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.

Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.

I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.

Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.

Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.

View 9 Replies View Related

Copy Range In Macro Using Variable Cell

Oct 12, 2011

I wish to copy a range which has the variable adderss in a cell.

Example the address is located in D2 and the address in that cell is B4:R113

I want to select the information in D2 for copying.

View 4 Replies View Related

Macro To Copy & Paste Variable Range

Aug 15, 2007

I am trying to write a very basic macro to copy and paste, but when I record the keystrokes, step 4 (see below) seems to be recording absolute cells and not relative (?). At any rate, I can't seem to figure out how to get it to select the text the way I want it to. These are the keystrokes I want:

1. Ctrl-c in the current cell <copy the current cell>

2. DownArrow <move down one cell>

3. Shift(hold)-End-Down <select the blank spaces from here down until the next occupied

4. Shift(hold)-Up <move the selection up one from the bottom to exclude the occupied cell>

5. Ctrl-v <paste>

6. End-Down <move down to the last occupied cell>

I have set the record tool to record "relative" references.

Below is the code that was recorded. It's line 5 that's not working - how do I fix it? What is the command to tell the program to press the shift key and an up arrow?? Such a simple thing, and yet I can't find the answer anywhere! Things sure have gotten complicated since the old Lotus 1-2-3 macro language...

Sub Fill()
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub

View 8 Replies View Related

Vlookup In Macro With Variable Offset Range

Jun 13, 2008

The message shows me "Can not get the vlookup property of worksheetfunction class" and don't know the reason. The 'plancomm' and 'bonus' is the range name in different worksheets of the same workbook as the rngcell.

Case Else:
.Offset(0, 1).Value = WorksheetFunction.VLookup(rngCell.Offset(0, -3), Range("plancomm"), 2, 0)
.Offset(0, 2).Value = WorksheetFunction.VLookup(rngCell.Offset(0, -4) & rngCell.Offset(0, 1), Range("bonus"), 6, 0)

View 3 Replies View Related

Macro To Add Conditional Formatting To Variable Range

Jun 16, 2008

I have 2 columns 'C' and 'D' which I want to apply Conditional Formatting to (i.e. colour the background of the cell in column 'D' for the respective row in colum 'C') if they contain different values. I have the following

Sub CellCCondFormatting()
Dim j As Long
j = Range("C2"). CurrentRegion.Rows.Count
MsgBox ("1st: " & j)
Range("C2:C" & j).FormatConditions.Delete
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(1).Interior.ColorIndex = 3
Range("C2:C" & j).FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=D" & j
Range("C2:C" & j).FormatConditions(2).Interior.ColorIndex = 4
MsgBox ("2nd pass: " & j)
End Sub

It iterates through all rows in my CurrentRegion OK but the Conditional Formatting 'formula' operates on the wrong value in column 'D'. For example, when viewed via menu option Format > Conditional Formatting... row 2 column 'D's Conditional Formatting value is D1714, row 3 column 'D's value is 'D1715' and so on....................

View 2 Replies View Related

Automatically Start Macro On Any Change To A Specified Range

Jan 19, 2008

Within range Sheet1!D4:D423, how can I have a macro called RestartClock start any time the Enter key is selected within a cell for that range?

View 7 Replies View Related

Cell Value To Define VLOOKUP Range?

Feb 26, 2014

is there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.

eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".

I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100 =vlookup(D4,NAMED RANGE,2,false) 

the Named range would be a value in the same row ie B4 - being Male or Female

so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas  Z100  =vlookup(D4,B4.Value,2,false) 

otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).

Many more Excel Formulas  Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) ) 

View 2 Replies View Related

Copyrights 2005-15, All rights reserved