CODEWITHSUNDEEP
excelvbaexcel-indirect
Jan
Jan

Reputation: 47

How to use the indirect function in VBA in my code?

I need to use the indirect function in VBA. I want to indirect cell C15, or C14 and C15 if possible.

C14 displays the sheet name: Data1 C15 displays the range: J3:J45999

This should follow the same concept as the indirect function in excel: =indirect(C14&"!"&C15)

Private Sub Unique_Click()

Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next

Set xRng = Worksheets("Data1").Range([indirect("c15")]).Select
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("B21")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("B21:B" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo


End Sub

Upvotes: 0

Views: 2840

Answers (2)

Jan
Jan

Reputation: 47

I made it:) Used the following code:

Private Sub Unique_Click()

Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next

Set xRng = Worksheets("Data1").Range(Range("C15"))
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("B21")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("B21:B" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo


End Sub

Upvotes: 0

Harassed Dad
Harassed Dad

Reputation: 4704

No need to use indirect in code, you can just explicitly refer to the contents of the cell

Set xRng = Worksheets("Data1").Range(Worksheets("Data1").Range("c15").text)

However your code uses a mix of explicit sheet references (Worksheet("data1"), implicit references (range("B21") will use whatever sheet is active at that moment) and references to the activesheet object. I suggest you explicitly specify which sheet you mean at every point.

Upvotes: 1

Related Questions