Excel Vba Range Column

Select method?. This is a good technique to employ if a user would like to see where a list of strings appear in a workbook. VBA- how to select a range of columns? In all of the instruction material I have read, this question is never answered. The tutorial shows how to convert row to column in Excel using the Paste Special feature, TRANSPOSE function, VBA code, and a special tool. vba range offset to fixed column position Hi, I am looking for a way to offset a selected range (always variable in position, number of rows and columns) to a fixed column position. This thread is locked. When you're working in VBA, you'll have to tell Excel which cells you want a particular command to apply to. In VBA for Microsoft Excel, to programmatically refer to a column, you use a collection. It is opposite of union of ranges. 43 zeros in the cell. Is there a VBA function to get the first and last row and column of a range? Bob · Without SpecialCells: Dim r As Range Set r. Easily sort cells in each column or row of a range. Now I will warn you upfront that the shortcut method is never the quickest, but for me the clarity and simplicity it provides usually wins out. Take the upper-right, upper-left, lower-right, and lower-left cells that you’ve done anything to — entered any content, changed any formatting, including cell color, font, etc. In many circumstances I prefer to use the shortcut brackets, []. When you manipulate or loop through range names in VBA you will need to use the Names collection. In your code, one of the classes you can use to access a column is named Range. How can I use a dynamic named range as a RowSource in a userform Combo Box. Excel has included a function that allows you to automatically set the width of a column to fit the longest field it contains. Activate the Range: 8. LEARN from the Collection of MS EXCEL VBA Macros Codes/Examples Macros Collection) 17 ways to OPTIMIZE VBA Code for FASTER Macros based on a column's value;. In the code above, we start with the Range B1. If you remove all the columns, the data should simply aggregate with the final total remaining the same. The following code line selects the entire sheet. Select is the same as Range("AE11"). In VBA the syntax is: Sub AddComment2Table() Dim oSh As Worksheet Set oSh = ActiveSheet 'NoGo. VBA Arrays And Worksheet Ranges. expression. expression. On this page are examples on how to do it. expression A variable that represents a [Range](. range_lookup(optional) – Use True(default) to find closest match. Loop through the columns in a range. Try this in Excel:. This will help you to know how to delete specific Columns in a range from Excel workbook using VBA. One of the most powerful features of Excel is the ability to write programs in Visual Basic for Applications that run "behind" your worksheets to turn Excel into a calculation-oriented development platform for creating special purpose spreadsheets which can function as applications in their own right. Assume that you have a column letter D, you want to convert to number 4. 18) 全てのシートの行を非表示にするExcelマクロ (2017. Row 'Referring to the row; returns 3 Range("B3"). We can call these cells selRow & selCol. We’ll briefly explore one simple example of using VBA in Excel to extract values from one worksheet , but this is just the tip of the iceberg for what Excel and VBA can accomplish together. The remainder of the post provides the most complete guide you will find on the VBA arrays. The QueryTable object has a native Excel feature to refresh data. Let's say you have data in cells A1, A2, A3 and A4 and you would like to select the last non-blank cell which would be cell A4 in the column. Excel VBA - Copy Range into Array - Setup. When you record macros in VBA and select a range of cells, Excel uses the range object. Select is the same thing as Range("A1"). When applied to a Range object that's a multiple-area selection, this property returns columns from only the first area of the range. Next, it is explained how to loop over the columns collection. Selecting multiple ranges in Excel VBA helps your code to work faster. An entire row or column can also be a range. VBA Delete Column. Step 3: Unleash the VBA magic. It allows you to select an entire cell range and concatenate the values easily, without the need to select each cell as an argument. The other two values are not shown in column C and D (they would if we had defined Range(“PrimaryColours”) to be B2:D4). This article will show you how to use Range, Cells, Offset and Resize to select a range in Excel VBA. This page describes VBA functions that can be used to find all the occurrences of a value on a single worksheet or on multiple worksheets. Copy Named Range in Excel VBA I was playing around with row counts and column counts with the named ranges but with no success. How do I put data from multiple columns into one column with Excel formula; How to transpose columns into single column with VBA macro in excel. We are using a loop for this. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. “A list is only as strong as its weakest link” – Donald Knuth. EntireRow can also be useful if you have a range for a single cell but would like to reach other cells on the row, akin to a LOOKUP. Like anything in Excel and VBA, there are many different ways to accomplish this. Referencing ListObjects (tables) with VBA By camwallace VBA 3 Comments This is about how to the reference a table (a. An Excel worksheet—more generally, any spreadsheet—is an array. Excel VBA - Cells Property. Select is the same thing as Range("A1"). All the methods and properties that apply to Cells, Range, Rows or Columns also apply to Activecell and Selection. Excel VBA Find String in Range – This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string. If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash. Application. Returns the number of the first column in the first area in the specified range. Also, besides the Define Name function, you can use VBA to select entire column but first row. First we will show you how to paste or insert copied rows/columns and discuss the differences. Summary: Learn four different ways to find the last cell, row or column in a worksheet using Excel VBA. I have used this code to find the first blank cell in column A…i need to find the first blank cell in Column F or others. Thanks for any help. You can select multiple ranges in single line code and perform the action you want to perform. Excel convert column letter to column number. The ActiveCell is the selected cell or the first cell of the range that you have selected. CutCopyMode = False Thanks! This thread is locked. A frequently asked question is how to hide rows and/or columns in Excel using a VBA macro. Writing Your First VBA Function in Excel About User Defined Functions. CountA(Sheets("Sheet1"). I Googled for a solution and found many tips, but I wanted to use the simplest way to do it. hi, I am looking for a macro which will add a column to my sheet and fill a code which is related with my excel file name, I mean to say that suppose there is a file name test which is save in my desktop and I want to open it and run an macro which will add a column to test one or it save another file as test1( both will work) and in that test1 it will add a column say range ("f1:f & bottom. net Search Site:. In VBA for Microsoft Excel, to programmatically refer to a column, you use a collection. One of the Excel queries I often get is – “How to highlight the Active Row and Column in a data range?” And I got one last week too. Then the range of blank columns, then we have used “Do until loop” to delete the blank columns. You may also want to read: Excel VBA consolidate multiple workbooks into one workbook. net Search Site:. Select a cell of the column you want to select and press Alt + F11 to open the Microsoft Visual Basic for Applications window. You can, with Excel VBA get column letter or letters in two basic ways. Find Code Example; Range. Gomez In this VBA Tutorial, you learn how to use Excel VBA to set or autofit the width of columns in a variety of circumstances. FillBlanks -- works in Excel 5 version and up Provides a solution for Excel 5, range in single column only. Hello, Please could you tell me how to express the range Range( B2:B10 ) in terms of row numbers and column numbers? I'm thinking something along the How to set a range using row numbers and column numbers?. As the range has only two rows the entire column is considered B1:B2. Item(1, 1) Cells. Copy the following code in a general module and call the TestRun function with required ranges to see how it works. A range can be a cell, row, column, or a grouping of any of these. To refresh data inserted using ADO, just insert the data again. For example: Cells(1,1). Occasionally, you might find it necessary to copy and paste a column of data from one Excel. This is the snippet Create a Unique List from Column A (Excel Macro) on FreeVBCode. The name of the worksheet is 'Agent'. The object Selection comprises what is selected. Normally we code a range by writing it within "" as under. This is a good technique to employ if a user would like to see where a list of strings appear in a workbook. Like anything in Excel and VBA, there are many different ways to accomplish this. How to concatenate a range of cells in Excel. We have executed all these codes on sheet1 of macro file. The ActiveCell is the selected cell or the first cell of the range that you have selected. This method only works if there is a perfect table - you have the same amount of data in each column. expression. Range("Risk") ' The statement simulates activating the last cell in column A, pressing End, ' pressing Up Arrow, and then moving down one row. you can fit 8. The code below works to do this. Select the Last Non-Blank Cell in a Column. The macro recorder has its own preference when writing to ranges but it's not the only way. Columns property. Excel VBAで列番号を取得するには、RangeオブジェクトのColumnプロパティを利用します。 Rows・Columnsで自動メンバー表示を from インストラクターのネタ帳 Rows・Columnsの入力時に引数指定をあと回しにすると自動メンバー表示機能を使えます。. Select The columns, however are reliant upon a variable, therefore I would like to be able to use that variable to change the columns when selecting this range. Additionally, when using the Cells property of a Range object, the row and column index numbers can be 0 or negative. Excel VBA unmerge columns automatically For some systems, when you export a non-Excel report (e. Find Code Example; Range. com: Excel Tables (VBA) Removing formating from an Excel Table. expression A variable that represents a Range object. It has rows and columns, and it has cells that represent the intersections of the rows and columns. Excel VBA - Change cell color based on value [Solved/Closed] I've made a list and I want to go through a column (with an UNKNOWN set Range, items may be added or. Select is the same thing as Range("A1"). A 4 part section >> > BUY THE BOOK OF THIS COURSE ; The Excel VBA Resize Property. Transposing data in Excel is the task familiar to many users. Autofit If we find the width of a row or column to be disproportionate, then we can change it by using autofit property. This article will show you how to use Range, Cells, Offset and Resize to select a range in Excel VBA. 12 part section >> Excel VBA and Charts. Below code hides column C on the active worksheet. Single Column ComboBox. The range object will be composed of multiple areas and Excel will only return the count from the. Microsoft Excel provides a default appearance for a cell with regards to its background. Different database have different structure. Hi, I would like to ask a question on range. Columns(1) The Columns property is also available on any Range MSDN. Reading Values from one Cell to another. Well, we almost know what the first line in the code means: Range (B2:B {number of row of bottom-most non-empty cell in column A}): all the cells in column B from row 2 to the last row that contains data in column A. As we will see in various examples, you can directly access this class. To initialize the variable, you will identify the workbooks and the worksheets you are using. By “Excel VBA Copy Methods”, I mean the different methods you can use to copy data from one range or worksheet to another. Each time we copy a value to column B, we increment counter by 1. Often times we want to reference a column with numbers instead of letters in VBA. Moving the selection box two rows down and one column to the right: Selecting rows It is possible to select entire rows using the Range or Rows commands (the Rows command is of course specific to rows). The If statement checks the cell’s value to see if it equals X. Which columns are to be included is determined by the RowIndex and ColumnIndex properties as explained below. Excel VBA - Change cell color based on value [Solved/Closed] I've made a list and I want to go through a column (with an UNKNOWN set Range, items may be added or. In case you are wondering how to compare 2 columns of data having rows in millions & extract the unique values among both the columns then you should read this article. Using the VBA method you will also need to combine the Columns function with the Count function to return the total number of columns in a selected range. The range object will be composed of multiple areas and Excel will only return the count from the. Excel VBA macros are very helpfull to copy Excel Range to another sheet. Range(“B:B”). If you have more than one of them, just separate each number with a comma (e. This Excel tutorial explains how to use the Excel COLUMNS function with syntax and examples. I dumped a recordset (which will have varying sizes in my application) onto a worksheet beginning at cell A25. Range(“3:3”). This is the table we are going to use. The following macro contains various examples of how to reference one or more columns by the column number in Excel. Find Code Example; Range. Is there a VBA function to get the first and last row and column of a range? Bob · Without SpecialCells: Dim r As Range Set r. VBA code for Text to Column Here is a basic VBA code for converting Text to Column that works across multiple columns in an Excel workbook. The second line, Range("A5"). Finding the last used row, column, or cell is one very common task when writing macros and VBA applications. Excel Names That Reference Other Workbooks. In this way, we can delete the empty columns through VBA. Excel VBA Find String in Range - This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string. Often times we want to reference a column with numbers instead of letters in VBA. Reference a range by name: 2. To refresh data inserted using ADO, just insert the data again. Range("A1:C2"). Like anything in Excel and VBA, there are many different ways to accomplish this. One unit of column width is equal to the width of one character in the Normal style. This is a 3-part video series and you can also download the file that contains the code. Hi, I would like to ask a question on range. Hidden = True. Hiding one or more columns on a worksheet. Referring to Ranges & Writing to Cells in Excel VBA (Range, Cells, Offset, Names) It's important to be aware of the different ways you can write to Excel cells with VBA. Its purpose is to return a range that is a specified number of rows and columns from a reference cell or range. Row and Column are properties that are often used to obtain the numerical address of the first row or first column of a selection or a specific cell. In many circumstances I prefer to use the shortcut brackets, []. Range Objects. Order1 is the direction of the sort. To convert column number to column letter, make use of Indirect Function, which turns a Text into Reference. Excel convert data from column to row. Excel VBA Articles. Shortest Method To Reference A Single Row/Column of a Named Range in Excel? especially since the Excel object model exposes Rows and Columns in VBA. How to use named ranges in excel vba? Database sheets has several columns of data and if i want to insert a column at the A1 column then it messes all my. Find method that requires some amount of understanding. Hi, I would like to ask a question on range. A frequently asked question is how to hide rows and/or columns in Excel using a VBA macro. Columns("D"). Ethnicity Table The columns I used for the chart were Ethnicity, Georgia Percentage Positive, Georgia Percentage, and College Percentage (Figure 2). One important part of this is referencing the various ranges within a pivot table by their special VBA range names (which are actually properties of the Pivot Table object). com) A lot of VBA beginners start their career using Cells. To work around this problem, populate each cell in your worksheet one at a time from the array, instead of populating the whole range at one time. Which columns are to be included is determined by the RowIndex and ColumnIndex properties as explained below. It allows you to select an entire cell range and concatenate the values easily, without the need to select each cell as an argument. Often, we Excel users refer to ranges that need to move or expand in future versions of our reports. com courses again, please join LinkedIn Learning. The VBA Range Object represents a cell or multiple cells in your Excel worksheet. Excel Experts, VBA Tips, Concatenate Range This tip is about doing what the Excel CONCATENATE function should be doing, but isn't ! Suppose we want to create a comma-separated list from a range or 2 ranges. So if I have a range like "A6" or "$AB7" is. This function gives the difference of two ranges (output is range). Select <– this selects the entire column B of the spreadsheet. Zooming On A Range. Here is a very small but useful excel vba tip for you. You may want to add more data to the end of a column or you may need to select a range of used cells. Here is some code to move around and work with the components (rows, columns, cells and their values and formulas) of a worksheet. Summary: Learn four different ways to find the last cell, row or column in a worksheet using Excel VBA. Excel VBA Range: How to Use Range in VBA October 18, 2013 by Kasia Mikoluk Every Excel user is familiar with selecting a range of cells - by SHIFT + Clicking, CTRL + Clicking, or dragging the mouse over a range of cells. The For loop has two forms: For Next and For Each In Next. Both the Excel formula and VBA approach make use of the COLUMNS function to count the number of columns in a selected range. I'm not sure how to do that. The name of the worksheet is 'Agent'. When you need to select data in an Excel worksheet, you can use the Range object's Select method for a quick fix--but implicit referencing will allow you to write more efficient, maintainable code. LEARN from the Collection of MS EXCEL VBA Macros Codes/Examples Macros Collection) 17 ways to OPTIMIZE VBA Code for FASTER Macros based on a column's value;. Tables are a special type of range that offers the convenience of referencing named ranges, but they are not created in the same manner. Find method that requires some amount of understanding. The filterField is an integer representing the range column to filter. Today I am going to take on one of the most frequent question people ask about Excel VBA – how to the the last row, column or cell of a spreadsheet using VBA. Format : Cells(Row,Column) Excel is represented in a grid format, tabular form in rows and columns, so to reach to particular cell you need tell to compiler the cell is located in which row and which column in that particular row. You can also make your VBA much more readable, and eliminate the need for "variable formulae" by using the native Excel (R1C1) syntax. A Table is simply a structured range where you can refer to different sections that are automatically mapped out (such as the Header Row or the column below the header "Amount"). Excel Made Easy. vba disable insert row or column in Excel. When you manipulate or loop through range names in VBA you will need to use the Names collection. In your code, one of the classes you can use to access a column is named Range. This piece of the program looks as. It's quite common to need to know the last used row in a sheet or column in a spreadsheet. Yet Excel is capable of far more than the standard editing of cell contents allows, through the magic of Visual Basic for Applications scripts, or VBA. Select One Column of Data with VBA Let’s assume you aren’t using an Excel Table, List Object, or List for your data table and you want to select the data in only one column, which means the Header row is excluded. I have a table in an excel sheet and I want to select the entire first row. Finding the last used row, column, or cell is one very common task when writing macros and VBA applications. Select the Last Non-Blank Cell in a Column. Column Differences and Row Differences Methods Last Updated on Tue, 25 Jun 2019 | Excel VBA Macros The ColumnDifferences method returns a Range object that represents all the cells in the range whose contents are different from certain comparison cells (there is one comparison cell in each column). As with so many things in Excel there are a zillion different ways to refer to a range from VBA. Using iGrid as an Excel VBA grid control. Finding The Last Used Cell In A Range. Excel VBA Find String in Range – This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string. Offset(4, 3). When applied to a Range object, all addressing is relative to that range but the returned Range reference does not have to be part of the original range. To get a full set of relative column numbers in a range, you can use an array formula based on the COLUMN function. We have an Excel sheet that has a list of, for example, names in column A and we want to create folders for each of them on a disk. >> How to Sort Data in Excel using VBA (A Step-by-Step Guide) Excel already has a couple of ways to sort data quickly. This is a 3-part video series and you can also download the file that contains the code. People seem to get confused about entering dates. You feed it one of several contants (e. Here’s the workbook with code to remove columns from pivot table using vba. that apply to visible rows only. You may also want to read: Excel VBA consolidate multiple workbooks into one workbook. Select the Last Non-Blank Cell in a Column. Like anything in Excel and VBA, there are many different ways to accomplish this. Application. COLUMNS(array) The COLUMNS function syntax has the following argument: Array Required. This page describes how to transfer data between VBA arrays and worksheet ranges. A range is simply a cell or collection of cells that you tell Excel to pay attention to. myRow is an Excel. The range is particularly a data set in excel. In the code above, we start with the Range B1. Excel Formula Training. Insert a New Row or Column - Excel Keyboard Shortcut Lately, I have been trying to use as many Excel keyboard shortcuts as I can (not only for productivity, but for ergonomic reasons). vba disable insert row or column in Excel. Formulas are the key to getting things done in Excel. This Excel tutorial explains how to use the Excel COLUMNS function with syntax and examples. If you are using a variable to handle a range then you may need to identify what the first and last row and column numbers are that make up that range. Reference a range by name: 2. The Excel OFFSET function returns a reference to a range constructed in parts: a starting point, a row and column offset, and a final height and width in rows and columns. Search this site Data Dictionary in VBA - Complete Syntax Documentation posted Sep 30, 2013, lCols = oTarget. This is dynamic so as your Excel rows grow, the code adjusts accordingly. Turning off screen updating is separate from turning off the status bar display so that you can disable screen updating but still provide feedback to the user, if desired. 43 zeros in the cell. Outside our highlight range, lets set aside 2 cells (E17 & E18 in this case) for keeping the details of which row & column needs to be highlighted. In VBA for Microsoft Excel, to programmatically refer to a column, you will use a collection. Hiding a column can be simple. The syntax of Remove Duplicates in Excel VBA. If you are new to VBA, we suggest that you go through our basic Excel VBA tutorial. This is not about that. Excel Made Easy is a service to people looking to learn Microsoft Excel or any other spreadsheet SW on the market. Sub ShowArrowsExceptOne() 'shows all arrows except ' in specified field number Dim c As Range Dim rng As Range Dim i As Long Dim iHide As Long Set rng = ActiveSheet. FormulaR1C1 = "=SUM(R1C[-1]:RC[-1])" which means sum from row 1 of previous column till same row of previous column. What is the used range ? - well, it's meant to be a square around all populated cells. The following traps all of the items in column A and adds that to the autofilter output. OFFSET is handy in formulas that dynamically average or sum "last n values. In VBA to Split Data Range into Multiple Chart Series I shared a VBA procedure that split a range into separate series in a chart. [RESOLVED] Excel VBA to name a column range If this is your first visit, be sure to check out the FAQ by clicking the link above. Columns property. Well, we almost know what the first line in the code means: Range (B2:B {number of row of bottom-most non-empty cell in column A}): all the cells in column B from row 2 to the last row that contains data in column A. Excel VBAで列番号を取得するには、RangeオブジェクトのColumnプロパティを利用します。 Rows・Columnsで自動メンバー表示を from インストラクターのネタ帳 Rows・Columnsの入力時に引数指定をあと回しにすると自動メンバー表示機能を使えます。. I am back with so many interesting stuff for you in Excel VBA. Hi guys, I'm hoping you can help out. Excel has included a function that allows you to automatically set the width of a column to fit the longest field it contains. For example: Each month, we must report the current month, the year to date, and, perhaps, the twelve most-recent months of financial data. The macro will then popup with a message box. Microsoft Excel uses some default fonts to display the names of columns and rows on a worksheet. Excel VBA - Change cell color based on value [Solved/Closed] I've made a list and I want to go through a column (with an UNKNOWN set Range, items may be added or. Below code hides multiple columns (D. In Excel 2007, the VBA array is longer than 8,203 characters in length. Excel VBA: Copy a Table Column Unique Data to Another Table Column I have updated the functionality of my earlier post to only copy over unique values, then I added a sort on the values copied over. You can use Range. Step 2: Use 2 cells to capture the selected row & column details. In your code, one of the classes you can use to access a column is named Range. If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range, and the Row property returns the row number of the first row in the range. One of the biggest problems in maintaining "good" data is the entry of dates. I'm needing to run a macro that looks at column D in worksheet 8 to 13 for a date. To initialize the variable, you will identify the workbooks and the worksheets you are using. The problem is it only populates the range with the first value in the array. Many beginners start their career using Cells. Columns property. You can select multiple ranges in single line code and perform the action you want to perform. Excel VBA Find String in Range - This post shows three examples of how to use the Excel VBA Find method, searching a range of cells for a specific string. Most your time working with Excel is spend in the worksheet area - dealing with cells and ranges. For example, if you use VBA to copy/paste a range, while the paste is completing Excel will show the progress of that operation on the status bar. I have the row number and column number parsed ito variables. Finding the last used row, column, or cell is one very common task when writing macros and VBA applications. Hide columns using VBA in Excel. The Function parameter tells Excel what sort of subtotal to create. In this tutorial, you'll learn how to work with cells and ranges using VBA (selecting, copying, moving, and editing cells and ranges). It will save me some time and help the readers too. Excel Autofilter with VBA. Clear the Target Range : Always clear the target range (in Another sheet) before copying and pasting the data. As I alluded to earlier, try using column numbers rather than letters. As long as you're familiar with basic VB scripting and using more advanced. Named ranges make it easier to use and identify data when creating charts, and in formulas such as:. However, I seek to use multiple columns from a table. Don't confuse the Column and Row properties with the Columns and Rows properties. What you’ve got is the right code if all you want to do is to hide (or show) certain (or all) columns. Excel, Access, PowerPoint and Word Help > Forum > Tips, Tricks & Downloads (No Questions) > Excel Tips and Tricks > Split Range into Multiple Columns VBA If this is your first visit, be sure to check out the FAQ by clicking the link above. This article contains a sample Microsoft Visual Basic for Applications (VBA) macro (Sub procedure) to accomplish this. When applied to a Range object that's a multiple-area selection, this property returns columns from only the first area of the range. You can easily sort a data set by using the sort icons in the ribbon or the sort dialog box. RangeとCellsの使い方|ExcelマクロVBA入門 4. The counter holds track of the number of cells that have been copied to column B. This is a good technique to employ if a user would like to see where a list of strings appear in a workbook. Row and Column are properties that are often used to obtain the numerical address of the first row or first column of a selection or a specific cell. [crayon-5db74596aaec6540818568/] The code below relates to the data shown […]. 12 part section >> Excel VBA and Charts. Try it and see which cells are selected in the line above. >> How to Sort Data in Excel using VBA (A Step-by-Step Guide) Excel already has a couple of ways to sort data quickly. Range MsgBox rng. I need to export the contents of about 30 queries, each into a specific sheet, and cell range, of an existing Excel workbook. The range is particularly a data set in excel. It is the most important object of Excel VBA. Go through commented code (green) and change where dictated appropriate; noted as such. Step 3: Unleash the VBA magic. Range("A1:C2"). List Files in a Folder and Rename them – Batch Renamer By Ejaz January 1, 2014 ( 65 ) Some of the tasks I had to do at work involved running a couple of “master” spreadsheet models every month for new market conditions, for a lot of clients. OFFSET is handy in formulas that dynamically average or sum "last n values. You may also want to read: Excel VBA consolidate multiple workbooks into one workbook.