R

R1C1 references absolute references,

122-123 advantages of, 117-120 case study, 121 column numbers, remembering, 125-126 conditional formatting array formulas, 129 case study, 128-129 setting up in user interface, 126-127 setting up in VBA, 127 displaying, 118-119 entire columns/rows, referencing, 123 mixed references, 123 multiplication table example, 124-125 relative references, 121-122

R1C1Style() function, 121

radar charts, 196

random numbers, creating, 101

Range() function, 62

ranges, 61, 368

Advanced Filter, 215-218 cells empty cells, checking for, 69-70 first non-zero-length cell, finding, 91 selecting, 71-73 creating from overlapping ranges, 69 defining, 368-369 duplicates, removing, 89-90 formatting, 369-370 joining, 68-69 maximum values, returning,

99-100 named ranges, 62-63 checking existence of, 138

creating, 132-133 deleting, 134 global names, 131 local names, 131 overwriting, 133 VLookups, 139-140 non-contiguous ranges, 74 offsetting, 65-66 properties Cells, 64-65 Columns, 68 CurrentRegion, 70-71 Offset, 65-66 Resize, 67 Rows, 68 referencing in other worksheets, 63 relative to other ranges,

63-64 with Offset property, 65-66

resizing, 67 selecting with Cells property,

64-65 with CurrentRegion property, 70-71 shortcuts, 62 specifying with Columns and Rows properties, 68 with top-left and bottom-right corners, 62

Word ranges, 368-370 RangeText() function, 368 reading

Access databases, 404 CSV files, 298-299 text files delimited files, 392-394 files with less than 65,536 rows, 387 files with less than

98,304 rows, 394-397 files with more than

98,304 rows, 397-398 fixed-width files,

388-391 one row at a time, 395-397

ReadLargeFile() function,

397-398 ReadTxtLines() function, 298-299

Ready mode, 337-338 Record Macro button (Visual Basic toolbar), 13

Record Macro dialog box, 15-16

Record New Macro command (Macro menu), 15

recording macros, 22

absolute references, 25-27 preparation, 21 Record Macro dialog box, 15-16

relative references, 25-27

records above-average records, returning, 224 adding, 404 deleting, 404, 410 inserting, 404-406 no matching records, handling, 224 retrieving, 406-407 summarizing, 410-411 updating, 408-409

recordsets, filtering with pivot tables, 274-276

RefEdit control, 438

references

A1 references, 117 custom objects, 421-422 R1C1 references absolute references,

122-123 advantages of, 117-120 case study, 121 column numbers, remembering, 125-126 conditional formatting,

126-129 displaying, 118-119 entire columns/rows, referencing, 123 mixed references, 123 multiplication table example, 124-125 relative references, 121-122

ranges in other worksheets, 63 Offset property, 65-66 relative to other ranges,

63-64 shortcuts, 62 relative references, 25-27 Word objects, 363-364 References command (Tools menu), 360

RefreshAllWebQueriesO

function, 333 refreshing Web queries, 332

relative references, 25-27, 121-122 removing. See deleting Report workbooks, 250-251 ReportByMonth() function,

0 0

Post a comment