Excel VBA Lesson 4 Range
Activate
AddComment
AdvancedFilter
Calculate
Clear
ClearComments
ClearContents
ClearFormats
ClearHyperlinks
Copy
CopyFromRecordset
Cut
Delete
Find
Insert
Merge
PasteSpecial
------------------------------------------------------------------------------------
VBA Activate Range – Examples
Sub Activating_Single_Cell()
Range("A2").Activate
'or
Cells(2, 1).Activate
End Sub
------------------------------
Sub Activating_multiple_Cells()
Range("A2:C3").Activate
End Sub
-----------------------------------
Sub Activating_multiple_Cells()
Range(Cells(2, 1), Cells(3, 3)).Activate
End Sub
----------------------------------------------------------------------------
VBA Add Comments to Excel Range -
Range("A2").AddComment "Hello, this is a comment text"
-------------------------------------------------------------------------
Excel VBA Range Advanced Filter-
Sub VBA_Range_Advanced_Filter()
Range("A3:B8").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("B1:B2"), Unique:=False
End Sub
-----------------------------------------------------
VBA Range Calculate –
Sub VBA_Calculate_Range()
Range("A2:D10").Calculate
End Sub
--------------------------------------------------------
VBA Clear Range in Excel explained
Range("A2:D10").Clear
Excel VBA to ClearComments in a Range
Range("A2:D10").ClearComments
Excel VBA to ClearContents in a Range
Range("A2:D10").ClearContents
Excel VBA to ClearFormats in a Range
Range("A2:D10").ClearFormats
Excel VBA to ClearHyperlinks in a Range
Range("A2:D10").ClearHyperlinks
--------------------------------------------------------------------
VBA to Copy Range in Excel
Sub Copy_Range_To_Clipboard()
Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
--------------------------------------------------------------------
VBA to Copy Range to Destination in Excel
Sub Copy_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")
End Sub
--------------------------------------------------------------
VBA to Cut Range in Excel
Sub Cut_Range_To_Clipboard()
Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
--------------------------------------------
VBA to Cut and paste the Range to Destination in Excel
Sub Cut_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Cut Destination:=Range("E2")
End Sub
----------------------------------------------------------------
VBA to Copy Range in Excel –
Sub Copy_Range_To_Clipboard()
Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
------------------------------------------------------------
VBA to Copy Range to Destination in Excel
Sub Copy_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")
End Sub
-------------------------------------------------------------------
VBA to Copy Range in Excel
Sub Copy_Range_To_Clipboard()
Range("A2:D10").Copy 'This will copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
-----------------------------------------------------------------------
VBA to Copy Range to Destination in Exce
Sub Copy_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Copy Destination:=Range("E2")
End Sub
-----------------------------------------------------------------------
VBA to Cut Range in Excel
Sub Cut_Range_To_Clipboard()
Range("A2:D10").Cut 'This will cut the source range and copy the Range "A2:D10" data into Clipboard
'Now you can select any range and paste there
Range("E2").Select
ActiveSheet.Paste
End Sub
--------------------------------------------------------------------------
VBA to Cut and paste the Range to Destination in Excel
Sub Cut_Range_To_Destination_Range()
Range("A2:D10").Copy Range("E2")
'Or you can write as Range("A2:D10").Cut Destination:=Range("E2")
End Sub
---------------------------------------------------------
VBA to Delete Range in Excel
Sub Delete_Range_To_ShiftLeft()
Range("B2:D10").Delete Shift:=xlToLeft
End Sub
-------------------------------------------------
VBA to Delete Range in Excel – Example:Shift:=xlToUp
Sub Delete_Range_To_ShiftUp()
Range("B2:D10").Delete Shift:=xlToUp
End Sub
-----------------------------------------------------
VBA to Delete Range in Excel – Example: EntireRow
Sub Delete_Range_EntireRow()
Range("B2:D10").EntireRow.Delete
End Sub
------------------------------------------------
VBA to Delete Range in Excel – Example: EntireColumn
Sub Delete_Range_EntireColumn()
Range("B2:D10").EntireColumn.Delete
End Sub
----------------------------------------------------
VBA to Find Value in a Range
Sub Find_Range()
MsgBox Range("B2:D10").Find(What:="ab").Address
End Sub
VBA to Find Value in a Range –
Sub Find_Range_After()
MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address
End Sub
VBA to Find Value in a Range – MatchCase
Sub Find_Range_CaseSensitive()
MsgBox Range("B2:D10").Find(What:="ab", After:=Range("B3")).Address, MatchCase:=True
End Sub
------------------------------------------------------------------------------------------
VBA Insert Range in a Worksheet – xlDown
Range("C7").Insert Shift:=xlDown
VBA Insert Range in a Worksheet – xlToRight
Range("C7").Insert Shift:=xlToRight
VBA Insert Range in a Worksheet – EntireRow
Range("B2:D10").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
VBA Insert Range in a Worksheet – EntireColumn
Range("B2:D10").EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
--------------------------------------------------------------------------------------
VBA Merge Range –
Range("A1:D10").Merge
VBA Merge Range – Example2
Range("A1:D10").Merge(True)
VBA Merge Rows-
Range("5:10").Merge
VBA Merge Column –
Range("B:E").Merge
---------------------------------------------------------------------------------------
VBA PasteSpecial method of Range –
Sub Range_PasteSpecial_Values()
Range("C6:D11").Copy
Range("G6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
----------------------------------------------------------------------------------------
Paste Special properties
xlPasteValues
xlPasteComments
xlPasteFormulas
xlPasteFormats
xlPasteAll
xlPasteValidation
xlPasteAllExceptBorders
xlPasteColumnWidths
xlPasteFormulasAndNumberFormats
xlPasteValuesAndNumberFormats
xlPasteAllUsingSourceTheme
xlPasteAllMergingConditionalFormats
xlPasteSpecialOperationAdd
xlPasteSpecialOperationSubtract
xlPasteSpecialOperationMultiply
xlPasteSpecialOperationDivide
xlPasteSpecialOperationNone
|
Code |
|
How
to Range value assign. |
|
Range("A1").Value = "Hello" |
|
How
to Range value assign . With using
application , workbooks and worksheets object |
|
Application.Workbooks("create-a-macro").Worksheets(1).Range("A1").Value
= "Hello" |
|
Using the worksheet name. |
|
Worksheets("Sales").Range("A1").Value
= "Hello" |
|
Using the index number (1 is the first worksheet starting from
the left). |
|
Worksheets(1).Range("A1").Value =
"Hello" |
|
3. Using the CodeName. |
|
Sheet1.Range("A1").Value = "Hello" |
|
Value Put in range |
|
Range("B3").Value = 2 |
|
Value Put in Multiple range |
|
Range("A1:A4").Value = 5 |
|
Below Range Assign and Interior Color Green |
|
Dim Rng As Range |
|
Set Rng = Selection |
|
Selection.Interior.Color = vbGreen |
|
Value Put in Multiple range |
|
Range("A1:A2,B3:C4").Value = 10 |
|
Range Value copy |
|
Range("C3:C4").Value =
Range("A1:A2").Value |
|
Range("A1").ClearContents |
|
Range("A1").Value = "" |
|
Range CurrentRegion |
|
Range("A1").CurrentRegion.Select |
|
Resize |
|
Range("A1:C4").Resize(3, 2).Select |
|
value put B3 Cell |
|
Cells(3, 2).Value = 2 |
|
value put A1 AND D1 Cell |
|
Range(Cells(1, 1), Cells(4, 1)).Value = 5 |
