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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Hide Columns Starting From Active Cell
Continuing with a large Macro I am trying to peice together, I have reached a hurdle where I need to hide all columns to the left of the ActiveCell (which varies week to week) back to column B. I have the following code which selects the correct number of cells to the left of the active cell but doesn't highlight the whole columns - just 3 cells for each column (very odd) ActiveCell.Offset(0, -1).Range("A1").Select Range(Selection, "B" & Selection.Column).Select I'm sure it must be something to do with the Selection.Column part as when I write Selection.Row it just does 1 cell for each column (as to be expected) I should mention I already have the coding to hide the columns, just need a way to highlight the relevant ones.
View Replies!
View Related
Sort Area Around Active Cell By Two Columns
I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy. Dave supplied me with a macro that will sort a range by the first column: Sub SortThis() With ActiveCell. CurrentRegion .Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With End Sub I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.
View Replies!
View Related
Insert Rows At The Active Cell
I want to be able to allow a user to insert a number of rows (they would specify the number) into a worksheet, at the active cell location. I also need the same number of rows to be inserted into the same worksheet (but much lower down) The location of which is to be governed by the location of the original inserted rows. Having created the new rows I need all the formulas from the row ABOVE the active cell to be copied into all the newly created rows. example If active cell is A5 I want user to be able to run a macro which asks them how many rows they wish to insert. (Assume the user asks for 7). The macro then inserts that number of rows, (starting at row 5). And additionally adds 7 rows at a location further down the worksheet (say row 105). The formulas from the row above the active cell are then copied into all the newly created rows. 2nd example If the active cell was A9 then the macro would add 7 rows, starting at row 9 and additionally add 7 rows starting at row 109. Then formulas added.
View Replies!
View Related
Delete N Rows From Active Cell
Is there a quick way to delete "N" rows from the active cell regardless of the content of the cells in those rows? I import text files into excel then frequently have to go through them and delete rows of nonimportant data. For example if cell A57 is currently selected can I run a macro that will delete the next 10 rows?
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
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 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
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
Numbers Of Rows/Columns In Range
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 Replies!
View Related
Dynamic Range Of Columns With Fixed Rows
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 Replies!
View Related
Dynamic Range. Rows & Columns
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 Replies!
View Related
Unhide Rows/Columns Of Used Data Range
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...
View Replies!
View Related
Range Of Cells Of Known Columns But Unknown Rows
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).
View Replies!
View Related
Sum Range: Multiple Criteria In Rows & Columns
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.......................
View Replies!
View Related
Format A Range Of Cells, Columns And Rows Will Vary In Length
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?
View Replies!
View Related
Adjust Formula Range Reference As Columns/Rows Added
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
View Replies!
View Related
Reference Cell X Rows/Columns From Static Cell
I have a worksheet where everything in column A links to a different workbook. Does anyone know if there is a formula that can then look at the cell reference in column A and take the value from a set row in that same column? For example: The cell in column A2 is set to cell Q1 in a different workbook. I need a formula in cell C2 that will then bring in the value of cell Q64 in that same workbook. Therefore always bringing in the value of row 64 for whichever column the cell in column A is et to.
View Replies!
View Related
AutoFill Down Rows Based On Another Columns Last Cell
I have a workbook which has one sheet "Raw Data" where I will cut and past blocks of data of various numbers of rows. Another sheet "level one calcs" conducts various calculations on the raw data. I was wondering if there would be any way to create a macro which would copy down (autofill) the last row of my my calculation page for the exact number of rows I added into the raw data. The goal here would be so that I dont have to highlight and copy down the formulas on the calc sheet each time I add in new data.
View Replies!
View Related
Compare 2 Columns & Copy Common Cell Rows
this is in reference to a question I asked some time ago in which I was given the following: =IF( COUNTIF(SHEET1!$A$1:$A$1200,SHEET2!A1),SHEET2!A1,"") To summize as this is not quite working, I have two worksheets and in Column A are id#s. Worksheet 1 has100's more rows of data than Worksheet 2. I need to find the unique ids from column A in Worksheet 1 that match Unique IDs in worksheet 2. I need to copy the row of cells from worksheet 1 where the unique id equals that u.id in worksheet 2. That row needs to be copied to Sheet 2 that has the same unique id, and after the pre-existing cells which are already present. So for instance: if Sheet1 A100 = Sheet2 A24, then copy row A100from Sheet 1 and paste it to Row A24 of SHeet 2 start with the first empty cell- just say Sheet2 G24 as an example. Using the formula I was given before, it finds matches, but the pasted rows are off. If the match occurs with Sheet 1 A450 and Sheet 2 A36, it will copy the value from ROW/SHeet 1 A36 instead of ROW/Sheet 1 A450
View Replies!
View Related
VBA Macro To Filter Rows By Cell Value & Hide Blank Columns
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands. basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred. the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value. where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View Replies!
View Related
Reduce Number Of Active Rows In A Worksheet
i have data in 1 worksheet say sheet1 which i copy to another sheet say sheet2. i do some processing like using some formulas on the data from columns A to D and getting an output in column E in the same sheet. Now the problem is, before i start the calculations in sheet2, the file size is 400 kB... and after the calculations are done, it becomes 20 mB... i dont know why this happens... there are some things which i think might be a reason but not very sure... 1. i calculate the last row in column A using lastrow = Range("A65536").End(xlUp).row i tried Range("A500") and it seemed fine... Now u may tell me that i can just use 500 and make things simpler but its just a workaround and not a solution... 2. i use macros.. this is just an info... 3. Another thing is, if i use 500, the number of active rows is 500... i dont know whether the right word is active rows but wat im trying to say is, the scroll bar for the rows movement reaches row 500 when it reaches the bottom.
View Replies!
View Related
Extract All Matching Rows With Active From A Database
Heres what I need: On the 'values' worksheet (first one), a formula in cell M2 that will search the worksheet 'oc_users' (second one) for any row containing "Active" in column G, these rows will return the corresponding email address in column A of the same worksheet. Skipping "Inactive" rows. End result: anyone with an active status in oc_users will have their email populated in the 'values' worksheet.
View Replies!
View Related
Loop Instructions For Number Of Active Rows
First: I am using following loop code to repeat until the last row with data in column B has been reached. I keep getting a compile error and can't figure out to to fix it. The compile error occurs on the ".cell" and ".range" portion of the code and has indicated an invalid or unqualified reference. Second: Once I have the loop working, I need to reference different cells each time through the loop. The first time through the loop I need to reference cells B2 and C2....the second time cells B3 and C3.....and then B4 and C4....and so on. I am increasing the value of i each time but when I ran the code, I was not getting any results.
View Replies!
View Related
Turning 64 Rows And 18 Columns Into One Row With 1152 Columns
I have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented). In other words, my individual data files look something like this: Participant 1 (title of data file) Trial/var1/var2/var3 1 55 43 65 2 54 43 56 3 33 23 56 and I want to compile all the data into a single file that looks like this: Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ... 1 55 43 65 54 43 2 . . . I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.
View Replies!
View Related
Find Data In Column Range & Return Cell Same Row But X Columns To Right
i have is 3 sheets in the same excel document. Sheet 1 is the mater sheet, which is a compilation of sheets 2 and 3, however the sheet layouts are different. The sheets consist of a list of names and details. Sheet 1 has all the names in the list, however sheet 2 and 3 only have partial lists that are in a different order from the original list. The details listed next to the names in sheets 2 and 3 are different and hence a straight forward row copy and paste will not work. What i need is to write a vba script that can take the name from the master list, search sheets 2 and 3 for the name and lookup the variables placed in next to the name, then update the master sheet accordingly, then continue to the next name on the master sheet and do the update again, and so on until all is updated. I have already thought about using lookup functions in excel however there is 1000 names on the list and around 60 details so it would be messy.
View Replies!
View Related
Sort Range On Non Active Sheet
I'm trying to sort data on sheet1 and sheet2. I'm running all macros from sheet one and I need it to stay active all of the time. I'm using the following code to sort sheet2. When I sort the column on sheet2 with the code, I can not get back to sheet one without an error. Worksheets("Sheet2").Activate Columns("a:a").Select Selection.sort Key1:= Range("a1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I've tried Worksheets("Sheet1").Activate Columns("a:a").Select..............
View Replies!
View Related
Named Range That Refers To Active Sheet
is there a way to have the same named range cover a range of cells regardless of which sheet you are on? They seem to want to default to the page you are on. I want to make some VBA subroutines that will go over each sheet, and perform the same functions on each sheet. All sheets have the same format, but different data. I guess worst case scenario I can just break down and use multiple workbooks, but this seems a little less desirable. Should I be dividing this into seperate posts?
View Replies!
View Related
Apply Auto Filter To Active Range
I find it to be a little irritating that I have to remove the autofilter, then reapply it whenever I start entering data into a new column. I want a macro that I can assign to a button so all I need to do is hightlight the range of cells I want to apply the autofilter to, and click the button.
View Replies!
View Related
Copy Rows To Sheet Based On Cell Value In Range
a macro button on my excel sheet that should do the following. i have a range (Ex: E3:E46) which is being continuously filled, i need to transfer the information to another sheet depending on column "E", (Ex: if "E3" = "A" paste row to sheet2 if = "B" paste row to sheet3) and so on each row could have another string in column "E". after the paste is done it should clear the range making place for new entries, that should also be copied finding the next empty cell (it shouldn't delete the old entries)
View Replies!
View Related
Calculate X Number Of Rows In Range Based On Cell Value
I can't seem to make SumIF or Vlookup do what I want here. I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column. If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3) Period Target Actual %Target 1 74 68 91.9% 2 81 71 87.7% 3 76 87 114.5% 4 76 68 89.5% 5 71 89 125.4% 6 69 81 117.4%
View Replies!
View Related
Store Active Autofilter Criteria In Array Or Range
I want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column. .Autofilter.Filters(n).Criteria1 and .Criteria2 are great, but what if there are more than 2??? i.e. I have a column containing values L01 to L20. My column is filtered on L05, L06 and L07 (or some other combination). I want to extract the values and L05, L06 and L07 and do what I will with them.
View Replies!
View Related
Copy Range Of Cells To Active Row In Second Sheet
Sheet 1 has data entered into it, it is then printed out as a jobsheet, saved and the data cleared. There are certain fields on this sheet that are eventually manually replicated onto sheet 2. The row in which they must go on sheet 2 will always be the 'activerow' on that sheet from a previous operation. It would make life so much easier and save lots of time if I could incorporate copying cells C10,C12,K8,K12,M2,C27 and C29 from sheet 1 to respective cells H,I,J,M,N,R,S of the active row on sheet 2 before I carry out the clear data process.
View Replies!
View Related
Rows To Columns Or Columns To Row
I wrote a short VBA to change known data from rows to column and columns to row. Data is like this: Hello Hello Hello Hello Hello Hello I can chang it to Hello .... Hello or Hello.. . . .Hello I need to figure out a way to do this when data is not fixed - ie: Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello or Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello Can you offer suggestions on what commands I can use to find the begining and ending of data, so that no matter what format it is in I can change it to one row or one column?
View Replies!
View Related
|
TRACKER
