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
ADVERTISEMENT
Feb 23, 2013
why this won't work.
Code:
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
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
Next
I'd like to define the end of the range with more accuracy than I'm currently doing.
View 9 Replies
View Related
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:
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Sub CalculateATR()
Sheets("Data").Range("a1400").Select
Do
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Selection.Copy
Range("G5:K31").Select
ActiveSheet.Paste
Else
End If
End Sub
View 8 Replies
View Related
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
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
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()
Sheets("Source_Info").Select
Range("A2:D6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working_Data").Select
Range("A2").Select
ActiveSheet.Paste
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
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
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
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
Worksheets("FinalReport").Select
Range("e9").Select
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select...
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
ActiveCell.Range("A1").Select
ActiveCell.R1C1
Range("R1C1:R1C85").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Application. ScreenUpdating = True
Range("A1").Select
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
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
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
Feb 7, 2007
how to set up an array within a worksheet
View 3 Replies
View Related
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
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
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
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
cell>
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()
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A17").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub
View 8 Replies
View Related
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
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
Range("C2").Select
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
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
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
