Relative Referencing 101 Problems

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
62
Office Version
365, 2016
Platform
Windows
Hello,
I am trying to learn relative referencing for excel macros, and I am having embarassing problems. I tried setting up a very simple sheet like so:

1
151
2152
3
32
33

<tbody>
</tbody>

The first column starts in cell A1, and so forth. I click on relative reference, then record macro, and then do the following:

With the active cell in A1, do a Contrl Shift down arrow to select the range, click on the Data tab, click on Remove Duplicates, and end up with the correct result, which in this case was 3 remaining values. And I click Stop Recording. I set the same column up again and reran the macro. Correct again. When I try to run the macro for any other range, it doesn't work. It merely highlights the range. Is the fact I started the macro in cell A1 part of the problem, or should I have clicked on A1 to start recording? Thanks to anyone who could offer me some help.

Andy
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
It would help if could post the code that you've got.
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
62
Office Version
365, 2016
Platform
Windows
It would help if could post the code that you've got.
Sub Testrr()
'
' Testrr Macro
'


'
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range("$A$1:$A$5").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
The problem is that the second line of the code has the rangeA1:A5 hard coded.
Try
Code:
Sub Macro2()
    Range(selection, selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
 

Andy0311

Board Regular
Joined
Oct 16, 2019
Messages
62
Office Version
365, 2016
Platform
Windows
The problem is that the second line of the code has the rangeA1:A5 hard coded.
Try
Code:
Sub Macro2()
    Range(selection, selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
Thank you so much, Fluff. It worked perfectly. The code I sent you was produced from a macro. What did I do to hard code the range when I recorded? Again, thanks so much for your help. A
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback


What did I do to hard code the range when I recorded?
Nothing, it's just the way the recorder works.
 

Forum statistics

Threads
1,089,274
Messages
5,407,324
Members
403,134
Latest member
fmlp

This Week's Hot Topics

Top