Code is only working sometimes

[termeric]

I am trying to remove blank cells at the end of one of my sub routines. I've tried both sorting and deleting the blanks and in both cases it works just fine as long as I run the sub directly. However, this is set up to run as one of several subroutines that happens after a button is pressed. When its triggered through the button, the blank rows remain, creating problems downstream. Why does this seem to only work when I activate the sub directly?

Sub Regional_Legends()
'
' set the list for the regional legend based on years 1-4
'
Dim Row As Integer
'

'Copy Mkt, CFO, Segment from each for the 4 years
    Sheets("Year1").Columns("G:I").Copy Sheets("Regional Legend").Range("a1")
    Sheets("Year2").Columns("G:I").Copy Sheets("Regional Legend").Range("d1")
    Sheets("Year3").Columns("G:I").Copy Sheets("Regional Legend").Range("g1")
    Sheets("Year4").Columns("G:I").Copy Sheets("Regional Legend").Range("j1")
    
'count the number of used rows
Row = Sheets("Regional Legend").UsedRange.Rows.Count
    
'filter out the duplicates for each year
    Sheets("Regional Legend").Range("A1:C" & Row).RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
    
    Sheets("Regional Legend").Range("D1:F" & Row).RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
        
    Sheets("Regional Legend").Range("G1:I" & Row).RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
        
    Sheets("Regional Legend").Range("J1:L" & Row).RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
        
'recount rows
Row = Sheets("Regional Legend").UsedRange.Rows.Count

'combine the 4 years into one list
Sheets("Regional Legend").Range("D2:F" & Row).Copy Sheets("Regional Legend").Range("a" & Row + 1)
Sheets("Regional Legend").Range("G2:I" & Row).Copy Sheets("Regional Legend").Range("a" & Row * 2)
Sheets("Regional Legend").Range("J2:L" & Row).Copy Sheets("Regional Legend").Range("a" & Row * 3)
        
'recount rows
Sheets("Regional Legend").Range("D1:L" & Row).Clear
        
'filter out the duplicates for the combined list
Row = Sheets("Regional Legend").UsedRange.Rows.Count
Sheets("Regional Legend").Range("A1:C" & Row).RemoveDuplicates Columns:=Array(1, 2, 3), _
        Header:=xlYes
    
'isolate the Plan Market
Row = Sheets("Regional Legend").UsedRange.Rows.Count
    
Sheets("Regional Legend").Range("A1:C" & Row).Copy Sheets("Regional Legend").Range("D1")

    Sheets("Regional Legend").Range("D1:F" & Row).RemoveDuplicates Columns:=Array(1, 3), _
        Header:=xlYes
 
Sheets("Regional Legend").Columns("E").Delete

'Sort out blanks    
'Sheets("Regional Legend").Columns("A:C").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
'Sheets("Regional Legend").Columns("D:E").Sort Key1:=Range("E1"), Order1:=xlAscending, Header:=xlYes


'Delete Blanks
Dim rng As Range

  On Error GoTo NoBlanksFound
    Set rng = Range("A1:E" & Row).SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0

  rng.Rows.Delete Shift:=xlShiftUp

Exit Sub


NoBlanksFound:



    
    
End Sub

 

[MARK858]

Try replacing

  On Error GoTo NoBlanksFound
    Set rng = Range("A1:E" & Row).SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0

with

  On Error GoTo NoBlanksFound
    Set rng = [COLOR="#FF0000"]Sheets("Regional Legend").[/COLOR]Range("A1:E" & Row).SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0