Macro doesn't run in full from button control?

[adambc]

I have a macro that does a number of updates to my source data and finally sorts by column A and highlights duplicates in column A using this code ...

'Sort by ID

Dim rg As Range: Set rg = Range("A2:A" & lRow)
rg.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes

'Highlight duplicate IDs

'clear any existing conditional formatting
rg.FormatConditions.Delete

'identify duplicate values in rg
Dim uv As UniqueValues: Set uv = rg.FormatConditions.AddUniqueValues
uv.DupeUnique = xlDuplicate

'apply conditional formatting to duplicate values
uv.Interior.Color = vbCyan

It runs perfectly from the VBA "console", but when I assign the macro to a control button, this final part of the code doesn't run (the previous code runs fine!) ...

Any ideas?

 

[NdNoviceHlp]

A couple of things. "lRow" has not been set and you need to specify the sheet...

Sheets("Sheet1").Range("A2:A" & lRow)

HTH. Dave

 

[adambc]

A couple of things. "lRow" has not been set and you need to specify the sheet...

Sheets("Sheet1").Range("A2:A" & lRow)

HTH. Dave

lRow is set higher up in the code - do I need to set it again?

 

[adambc]

FIXED!

I needed to activate the Worksheet where the data is - even though the rest of the code had already executed on the correct WS - strange!!!

 

[smozgur]

FIXED!

I needed to activate the Worksheet where the data is - even though the rest of the code had already executed on the correct WS - strange!!!

The reason it worked when you activated the worksheet is you originally used the Range object without its parent, and the parent is set to the active sheet as default. I have changed the marked solution post because @NdNoviceHlp provided the correct method and actually code to make it work correctly. This thread will be more helpful to the future readers in this way.

When you use the Range object with its parent then you don't need to activate the worksheet but directly refer to the worksheet that needs to be used. It is the best practice while creating applications in VBA.

Sheets("Sheet1").Range("A2:A" & lRow)

 

[adambc]

The reason it worked when you activated the worksheet is you originally used the Range object without its parent, and the parent is set to the active sheet as default. I have changed the marked solution post because @NdNoviceHlp provided the correct method and actually code to make it work correctly. This thread will be more helpful to the future readers in this way.

When you use the Range object with its parent then you don't need to activate the worksheet but directly refer to the worksheet that needs to be used. It is the best practice while creating applications in VBA.

Sheets("Sheet1").Range("A2:A" & lRow)

Duh, I knew that and forgot!!!

Thanks for reminding me …

 

[smozgur]

Thanks for reminding me …

 

[NdNoviceHlp]

Thanks smozgur for your input/intervention. Have a nice day. Dave