ExcelMadeEasy: Vba advanced range methods in Excel

Vba advanced range methods in Excel

We had a brief introduction about Range Object and some of its methods and properties in the previous chapter.

Apart from the basic functionalities, there are some other methods which can be used to achieve advanced functions.

Some of the most often used functions are as discussed below.

RemoveDuplicates

This method is used to remove duplicate values from a range of values.The syntax for this method is as given below

Range.RemoveDuplicates(Columns,isHeader)

The following example code illustrates this

Sub removeduplicate()
MsgBox "Input test values from M6:M10"
Sheet30.Range("M6") = "Val1"
Sheet30.Range("M7") = "Val2"
Sheet30.Range("M8") = "Val1"
Sheet30.Range("M9") = "Val3"
Sheet30.Range("M10") = "Val2"
MsgBox "Remove duplicates!! Ready??"
Sheet30.Range("M6:M10").RemoveDuplicates Columns:=1, Header:=xlNo
MsgBox "Duplicates Removed!!! See M6:M8"
Sheet30.Range("M6:M10") = ""
End Sub

Autofill method

This method is used to perform autofill in a specified range.

This method might seem simple but without this, one has to go with For Loop which will make the program a little but slow

The following example illustrates this method

Sub Autofill_demo()
Sheet30.Range("N25") = 1

Sheet30.Range("N26") = 2
MsgBox "Lets autofill N25 to N40."
Set SourceRange = Worksheets("Q31").Range("N25:N26")
Set fillRange = Worksheets("Q31").Range("N25:N40")
SourceRange.AutoFill Destination:=fillRange
MsgBox "Autofill Completed!!"
Sheet30.Range("N25:N40") = ""
End Sub

Autofilter method

This method filters a list using Autofilter.This method takes multiple arguments.

The following example illustrates this

Sub autofiltr()
For i = 40 To 48
Sheet30.Range("N" & CStr(i)) = "Value " & CStr(i)
Next i
MsgBox "Lets Input sample values in N40:N48"
Sheet30.Range("N40:M40").AutoFilter Field:=1, Criteria1:="Value 44"
End Sub

excel vba advanced range methods

excel vba advanced range methods

excel vba advanced range methods

You can find similar Excel Questions and Answer hereunder

1) How can I find the slab in which a given value fits?

2) Is there a way I can average a range of numbers even if there is an error value in range?

3) How can I fill a range of cells with incremental times?

4) Sumifs with date range as criteria in Excel

5) How can I remove hyperlinks from a range of cells?

6) How can I quickly remove all blank cells in a data range?

7) How can I set FreezePanes in a certain range using VBA?

8) How can I set up a dynamic named range that expands automatically when new items are added to the list?

9) How can I get an estimate of Standard Deviation of a sample dataset?

10) How do I get the 2nd highest number in a range of numbers?

Here the previous and next chapter