VBA MOD01 Most Useful Range Properties and Methods

 




Most Useful Range Properties



Value Show the underlying value in a cell. This is the default property of the range object.

Read / Write
Cells Returns a cell or range of cells within a range object

Read / Write
End Returns the last cell of the range. Similar to Ctrl + ↓ or ↑ or → or ← Read-only
Offset Returns a new range based on offset row & column arguments

Read / Write
Count Returns the number of cells in a range.

Read-only
Column / Row Returns the column / row number of a range. If you select more than one cell, column /
row returns the first occurrence in the range.		Read-only
CurrentRegion Used with other properties such as .address returns the range of data Read-only
EntireColumn /
EntireRow		Returns a range object that represents the entire row or column Read-only
Resize Changes the size of the range by defining the rows & columns for resizing
Address Shows the range address including the $ signs. Read-only
Font Returns a font object that has other properties (e.g. bold) Read / Write
Interior Used with other properties such as .Color property to set colors Read / Write
Formula Places a formula in a cell. To make sure your VBA code is compatible with other
languages of Excel, your VBA formulas should use the English syntax. You can record
these with the macro recorder. The macro recorder uses FormulaR1C1 syntax. If
you’d like to have formulas in your language of Excel, you need to use FormulaLocal.		Read / Write
NumberFormat Define Number format (uses English version) Read / Write
Text Returns the data as string & includes formatting. Read-only
HasFormula Returns True, False or Null if the range has a mix Read-only




Most Useful Range Methods


Copy This is a practical method because it has paste destination as its
argument. This means you just need one line of code.

PasteSpecial Allows usage of Excel’s Paste Special options. To use more than one
option, repeat the line of code with the new option.


Clear Deletes contents and cell formatting in a specified range.
Delete Delete the cells and shifts the cell around the area to fill up the
deleted range.



 The delete method uses an argument to define how to
shift the cells. Select XLToLeft or XLUp.
SpecialCells



Returns a range that matches the specified cell types. This method
has 2 arguments. XlCellType is required (such as cells with formulas or
comments) and an optional argument to define more detail if constant
and formula cell type is used in the first argument.
Sort



Sorts a range of values
PrintOut Also a method of the worksheet object
Select Used by the macro recorder to select a cell but when writing VBA, it is
not necessary to select objects. Code is faster without selecting.






















Comments

Post a Comment

Popular posts from this blog

VBA01 Copy Resize Variably Sized Ranges

Image
 Copy Resize Variably Sized Ranges Public Sub CopyResizeVariablySizedRanges()     'COPY RANGE FOR VARIABLE SIZED RANGES     Range("A4").CurrentRegion.Copy Range("J4")          'OR FIXED RANGE     Range("A4:E10").Copy Range("J4")          'PASTESPECIAL METHOD TO USE EXCELS PASTESPECIAL OPTIONS          Range("A4").CurrentRegion.Copy     Range("J20").PasteSpecial xlPasteValuesAndNumberFormats               'TO ADD MORE PASTE SPECIAL OPTION TO ADD A NEW LINE     Range("J20").PasteSpecial xlPasteComments          'USE THE RESIZE PROPERTY TO RETURN A CHANGED RANGE     Range("A4").CurrentRegion.Offset(1, 0).Resize(Range("A4").CurrentRegion.Rows.Count - 1).Copy Range("A20")                End Sub
Read more

VBA MOD01 Passing Arguments to sub procedures (ByRef , By Val)

Image
  Private Sub myCalc(Getvalue As Double, myPercent)          Getvalue = Getvalue * myPercent     MsgBox Getvalue      End Sub Public Sub GetMyValue()     Dim myValue As Double     Dim p As Variant     'assign value and percentage from cell     myValue = Range("A8").Value     p = Range("B8").Value     Call myCalc(myValue, p)     MsgBox myValue      End Sub ========================================================================= Private Sub myCalc(Getvalue As Double, myPercent)          Getvalue = Getvalue * myPercent     MsgBox Getvalue      End Sub Public Sub GetMyValue()     Dim myValue As Double     Dim p As Variant     'assign value and percentage from cell     myValue = Range("A8").Value     p = Range("B8").Value     If Excel.WorksheetF...
Read more

PY MOD01 Lecture 01

 Coding Room ID -- rk.180497@gmail.com Pass - Ritesh@1804 https://app.codingrooms.com/management/courses/join-by-code/4J6slZE6 https://app.codingrooms.com/management/courses/6387/classes/8480/assignments https://replit.com/account ID - RiteshKumar144 ,  rk.180497@gmail.com Pass -  ================================================================== 1 0 0 D A Y S O F C O D E SYLLABUS BASIC Variables in Python String Manipulation Input and Print Functions Variable Naming Rules Mathematical Operations in Python DataTypes Converting types Conditionals IF/ELIF/ELSE Logical Operators Randomisation Error Handling Functions For Loops Code blocks and Indentation While Loops Flowchart Programming Positional and Keyword Arguments Python Dictionaries and Lists Nested Collections Returning Functions Return vs. Print Doc Strings vs. Comments Scope and Local/Global Variables Debugging Techniques     INTERMEDIATE       Local Development Environment Setup Py...
Read more