Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.
For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).
The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Return Active Cell's Location/row
I'm having trouble identifing a way to return a location for the position of the active cell. I've searched Excel help with "Position, location, return, activecell, etc." and I can't seem to figure this out. I know that it's possible, so that's why I'm on here! ... Ok, say the active cell is currently "F1", and I need the location "F1" to identify the ROW to be used in a formula later, how would I go about that? The current contents of cell "F1"' will be "REPLACE", but I need to change the words "REPLACE" in "F1" and other cells labeled "REPLACE" in column F to the following formula (where the "1" in "A1" is is the current row):
View Replies!
View Related
Merge Range Based On Active Cell Row
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this? If Sigma = 0 Then Selection.EntireRow.Insert ' New row for new entries ActiveCell.Value = "NONE" ActiveCell.Offset(1, 0).Select End If
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Cell Value Based On Mouse Location
Is there a way to have excel track where my mouse is hovering(say if I have it hovering over cell A4) and have it return the value of A4 in a different cell (e.g B7)? This would occur in real time - that is, as my mouse moved over the spreadsheet, B7 would update in real time. Note I would also accept someone selecting the cell - hovering (no select) would be ideal but selecting is OK.
View Replies!
View Related
Creating Variables Based On Cell Location
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
How To Select Range And Clear Based On LR Variable
I want to select and clear a part of a sheet. here is what I have. Sub clear_data() sheets("sheet1").select lr = activesheet.usedrange.rows.count Here is where I get lost in translation (syntax). I want to select starting at Bcolumn through bycolumn but the row be set with the LR from above, since the rows always change. I could write B2:by2000, but i want to use the LR variable to define the number of rows i have.
View Replies!
View Related
Select Dynamic Range Based On Non-empty Cells
I require code to identify the last row in column 'A' that contains data, and then to select every row up to that one, and each column up to 'H'. My data begins on row 3, and the rows with data varies from row 7 through 120. The columns with data is constant so there is no need to test in that direction.
View Replies!
View Related
Find Text Based On Active Cell Contents
I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.
View Replies!
View Related
Expand Active Cell To A Given Range
When i click commandbutton1: Copy Active cell (columnB), next cell (ColumnC), next cell (ColumnI), next cell (ColumnO) all of which exist in the same row. I will then paste the values into another sheet in the next available row which i think i have mastered. the cells will be pasted next to each other i.e. instead of column B,C,I,O they will be pasted as column A,B,C,D. My dilema is i am not sure if you can use multiple cell offsets to perform this or if it is better to use a loop.
View Replies!
View Related
Sum Range From Active Cell To Last Used
I Have searched existing threads but cannot find the answer to my query, As part of a longer macro, I want to sum all cells in a column which contains data. Each day the number of cells that contain data will vary. I have tired specifying all the cells in the column which contain data as a range, by selecting the top cell and using the Range(Selection, Selection, Selection.End(x1Down)) But I cannot get a SUM Formula of this range. If someone could show me the correct syntax to get a sum from a range like this I would be very grateful.
View Replies!
View Related
Range In Same Row As Active Cell
I'm looking for a piece of code, which would activate a certain Range i.e. the start of which would be in column A and the End in Column G. My problem is that the activated range of cells shuld be exactly in the same row as the currently active cell i.e. active cell B3 -> activated range A3:G3 .
View Replies!
View Related
Expand Active Cell To New Range
I have some working code that makes a number of changes to an excel report using VBA. Each time I execute the code I am taken to a different point in the excel report (depending on user inputs). What I now need to do is to change the cell selection from a single cell to three cells. For example, my code may result in cell B100 being the active cell. What I now want to do is change the selection from B100 to the range B100:B103. I would have thought I could write something simple like: Sheets("Group").Range("ActiveCell:Offset(0, 2)").Select I have tried every variation of the above but I can't seem to make it run. Where am I going wrong?
View Replies!
View Related
Copy Active Cell Down Adjacent Range To Right
Split from: Clear Range To Right Of Active Cell Down. I was working through this the first step was to clear the contents of the cells. After they are cleared, I have another sub that then goes and retreives a unquie number and places it back at the top of the column that I just cleared. Would this same code that cleared the cells contents work to copy the contents of the active cell down that same column, until the cell to its right is blank? -R-
View Replies!
View Related
Reference Cell Based On Button Location & Determine Button Used
I received this code from an example I found once upon a time that was originally submitted by someone else. Right now the code enters the current date in a cell of the same row as the checkbox when it's checked. I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro. First of all, I don't know how to reference a Forms button in VBA. Sub Process_CheckBox() Dim cBox As CheckBox Dim LRow As Integer Dim LRange As String LName = Application.Caller Set cBox = ActiveSheet.CheckBoxes(LName) ' Find row that checkbox resides in LRow = cBox.TopLeftCell.Row LRange = "B" & CStr(LRow) 'Change date in column B, if checkbox is checked If cBox.Value > 0 Then ActiveSheet.Range(LRange).Value = Date 'Clear date in column B, if checkbox is unchecked Else ActiveSheet.Range(LRange).Value = Null End If End Sub Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:.............
View Replies!
View Related
Range X Rows & X Columns From Active Cell
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.
View Replies!
View Related
Select Previous Active Window
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done? Sub AutoSave() dTime = Now + TimeValue("00:15:00") Application .OnTime dTime, "AutoSave" Windows("data.xlsm").Activate Sheets("Data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV ActiveWorkbook.Close Application.DisplayAlerts = True Windows(1).ActivatePrevious End Sub
View Replies!
View Related
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
Select Multiple Worksheets Based On Cell
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was Sub Selects() Dim strSht As String Sheets("Criteria").Select strSht = ActiveSheet.Range("L31") Sheets( Array(strSht)).Select End Sub
View Replies!
View Related
Select Sheet Based On Cell Content
I am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the sheets( range("c5")).select to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Select Cell Within A Range
how to select a cell in opposite (i.e right side of a range) while using a macro. My macro selects a certain range based on user input. The active cell is the left most upper cell. I need to move the active cell within the range to the right upper most cell. I tried the short cut key - tab, while recording a macro, but the macro just selcted a that certain cell.
View Replies!
View Related
Macro To Select Dates Based On Cell Values
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive. H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page. Below is what my limited understanding of VB came up with. Sub Date_Range() Dim First As Date Dim Last As Date First = Range("H4").Value Last = Range("I4").Value Sheets("Graph Data").Select Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _ , Criteria2:="
View Replies!
View Related
|
TRACKER
