VBA MOD01 Referencing Ranges

 Different Ways of Referencing Ranges


▪ Range ()
▪ Cells property of the range object
▪ Offset
▪ Defined Names
▪ Reference entire rows
▪ Reference entire columns
▪ Autofit all columns



Sub ReferToCell()

ActiveSheet.Cells.Clear
    
    Range("A1").Value = "1st" 'Cells(1,1)= "1st"
    Range("A2:C2").Value = "2nd"
    Range("A3,C3").Value = "3rd"
    Range("A" & 4, "C" & 4).Value = "4rth"
    Range("A5", "C5").Value = "5th"
    Range("A6:C8").Cells(1, 2).Value = "6th"
    Range("A1").Offset(7, 2).Value = "7th"
    Range("A1:B1").Offset(8, 1).Value = "8th"
    Range("LastOne").Value = "10th"
    

End Sub





Referencing Entire Rows/Columns 

Public Sub ReferencingEntireRowsColumns()

Range("2:5").RowHeight = 30 'ROW 2,3,4&5 HAVE HEIGHT OF 30

Range("7:7,9:9,11:11").RowHeight = 30 'ROW 7,9 &11 HEIGHT CHANGES
                                        ' TO 30 ROWS 8, 10 HEIGHT
                                         ' DOES NOT CHANGE
                                         
 Range("A:A , C:C").ColumnWidth = 20 'ONLY COLUMN A  AND C WIDTH IS
                                                                     'CHANGE WHILE COLUMN B IS UNTOUCHED
  

End Sub






















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