1 / 8

Mastering Excel Ranges: Comprehensive Guide

Learn all about working with ranges in Excel, from properties like Address and Font to methods like Copy and Sort. Understand how to reference ranges in VBA for efficient data manipulation.

ljohns
Download Presentation

Mastering Excel Ranges: Comprehensive Guide

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 6 Working with Ranges

  2. Important Properties and Methods of Ranges • Properties • Address. This property returns the address of a range as a string. Example: “B2:C6” • Cells. Returns a reference to a Range object and is usually used to refer to a particular cell. Example: Range(“A1:A10”).Cells(3) refers to the third cell in the range. • Example #2: Range(“A1:A10”).Cells(3,2) refers to the cell in the third row and second column of the range. • Many Excel objects have properties that are in fact references to objects down the hierarchy. The Cells property is an example. • Even though Cells is listed as a property of Range, the purpose of the property is to return an object. • Example: Range(“A1:G10”).Cells(3,5).Value where Cells(3,5) is a property of the Range(“A1:A10”) object, but returns an object which is the range E3. • The Value property returns the contents of cell E3.

  3. More Properties • Column: returns the number of first column in the range, where column A starts with the number 1. • CurrentRegion: Returns a reference to a range bounded by any combination of blank rows and blank columns. • Example: If the range is A1:B10 and C5:D8, the current region is the smallest rectangular area to cover this range (A1:D10). • Excel Pivot Tables use the CurrentRegion to guess where the data is located. • EntireColumn: Returns a reference to the range consisting of the entire columns in the range. • Example: Range(“A:B).EntireColumn.AutoFit • Font: Returns a reference to the font of the range . • Example: Range(“A1:D1”).Font.Bold = True • Formula: returns the formula in the range as a string. • FormulaR1C1: returns the formula in a range as a string in R1C1 notation. • Useful for formulas to be copied across or down • Example: Each cell in the range C3:C10 is the sum of the corresponding cells in columns A and B. • The FromulaR1C1 property of the range C3:C10 would be “=RC[-2]+RC[-1]”. • The R by itself means to stay in the same row. The [-2] and [-1] next to C reference two cells to the left and one cell to the left. • HorizontalAlignment: Returns the horizontal alignment of the cells in the range. Three possible values are xlRight, xlLeft, and xlCenter. • Interior: returns a reference to the interior of the cells in a range. Generally used to fill the color of cells. • Example: Range(“A1”).Interior.ColorIndex = 3, where 3 is color index for red.

  4. More Properties • Name: returns the name of the range. You can create a named range using the following code: Range(“B3:E20”).Name = “Sales”. • NumberFormat: returns the format code (as a string) for the range. Example: Range(“C3:C10”).NumberFormat = “#,##0.00”. • Debug.Print Range(“C3”).NumberFormat will print the number format to Immediate window in VBE (Crtl + G) • Offset: returns a reference relative to a range, where range is usually a single cell. • Row, EntireRow: similar to the Column and EntireColumn properties. • Value: generally used for a single-cell range where it returns the value in the cell which could be a label, number, or result of a formula. • Note: Range(“A1”).Value = 10 can also be written as Range(“A1”) = 10.

  5. Methods • Clear: This deletes everything from the range, values and formatting. • ClearContents: This can be used instead of Clear to delete only the values and the formatting in place. • Copy: Copies a range and usually has a single argument named Destination which is the paste range. • Range(“A1:B10”).Copy Destination:=Range(“E1:E10”) • PasteSpecial: Pastes the contents of the clipboard to the range according to various specifications spelled out by its arguments. • Common option: Range(“C3:D10”).Copy • Range(“F3:G10”).PasteSpecial Paste:=xlPasteValues • Select: Selects the range, which is equivalent to highlighting the range in Excel. • Sort: Sorts a range of cells depending on the arguments used. • Example: Range(“A1;F19”).Sort Key1:=Range(“C2”).Order1:=x1Ascending, Header:x1yes • Where Key1 specifies which column to sort on, and Header argument specifies there are column headings at the top of the range that should not be sorted.

  6. Specifying Ranges with VBAMost Common Ways to Reference a Range • Use an address: Range(“A1”) or Range(“A1:B1”) • Use a range name: Range(“Sales”) assumes there is a range with the name Sales in the active workbook. • Use a variable for the range name: Declare a string variable and set it equal to the name of the range. • Example: SalesName = Range(“Sales”).Name • Then Range(SalesName) can be used • Use a Range object variable: Declare a variable as a Range object and define it with the keyword Set. • Example: Dim SalesRange As Range • Set SalesRange = Range(“Sales”).Name • SalesRange.Font = 12

  7. Specifying Ranges with VBAMost Common Ways to Reference a Range • Use the Cells property: Follow Range with Cells property, which takes one or two arguments. • Range(“B5:B14”).Cells(3) or Range(“C5:E15”).Cells(4,2) • Use the Offset property: Follow Range with the Offset property which takes two arguments. • Example: Range(“A5”).Offset(2,3) says start in A5, go 2 rows down and 3 columns to the right. • The first argument is the row offset, where a negative goes up and positive does down. • The second argument is the column offset, where a negative goes to the left and a positive goes to the right. • Either argument can be set to 0.

  8. Specifying Ranges with VBAMost Common Ways to Reference a Range • Use top left and bottom right arguments: Follow Range with two arguments, a top left cell and a bottom right cell, separated by commas. • Example 1: Range(Range(“C1”).Range(“D10”)) returns the range C1:D10. • Example 2: With Range(“A1”) • Range(.Offset(1,1). .Offset(3,3).Select • End With • This code selects the range B2:D4. The top left cell is offset by 1 row and column, B2. Bottom right cell is offset by 3 rows and 3 columns from A1 which is D4. • Use the End property: End-Arrow is used to select ranges in Excel if they are very large. • Example: Data are in A1:M100 range and you to select the range. Click A1, hold Shift key, press End and Down Arrow. • Using VBA code and direction constants: xlDown, xlUp, xlToRight, and xlToLeft • Example: With Range(“A1”) • Range(.Cells(1,1), .End(xlDown) .End(xlToRight)) .Select • The middle line selects a range that is specified by a top left cell and a bottom right cell. First argument .Cells(1,1) is same as A1. Second argument, .End(xlDown).End(xlToRight) is the equivalent to Range(“A1”).End(xlDown).(xlToRight) is at the bottom right of the rectangulare range.

More Related