VB error message "Merged cell", but no merged cells in sheet

[Alex B]

Hi,

set up this code to filter out unique names from a list of consultants:

Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False
        
    Sheets("Resurstid").Visible = True
    
    With Sheets("Resurstid")
    
    Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "H1:M2"), CopyToRange:=Range("H4"), Unique:=True
        
    End With
        
    Sheets("Resurstid").Visible = False
    
    Application.ScreenUpdating = True
    
   Sheets("Analys per konsult").Select
   
   
End Sub

Get the error message : 'Run time error 1004: We casn't do that to a merged cell'. The following part of the code is marked in the debugger:

    Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "H1:M2"), CopyToRange:=Range("H4"), Unique:=True

Though, there are no merged cells in the sheet where I try to do the advanced filtering. Where am I doing wrong???

 

[Stiuart_W]

Code worked for me

I suspect you do have a merged cell there somewhere

 

[Fluff]

Try adding a stop before each range, otherwise you are working on the active sheet rather than sheet Resurstid

With Sheets("Resurstid")
    
    [COLOR=#ff0000].[/COLOR]Range("A:F").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=[COLOR=#ff0000].[/COLOR]Range( _
        "H1:M2"), CopyToRange:=[COLOR=#ff0000].[/COLOR]Range("H4"), Unique:=True
        
End With

 

[Alex B]

Code worked for me

I suspect you do have a merged cell there somewhere

I have checked, and even selected the whole sheet "Resurstid" and unmerged in case there were some merged cells. Still get the same error message. Am I referring to the right sheet in the code????

 

[Michael M]

I'd agree with Stiuart....i'm guessing there is one there somewhere....maybe even a vertical merged cell ??
How about posting the worksheet to a hosting site like dropbox or mediafire and then posting a link back here.
That way someone may be able to take a look at it !
you could also run this macro on the activesheet and see if that detects any merged cells

Sub findmerged()
    Dim c
    For Each c In ActiveSheet.UsedRange
        If c.MergeCells Then
            MsgBox c.Address & " is merged"
        End If
    Next
End Sub

 

[Fluff]

I have checked, and even selected the whole sheet "Resurstid" and unmerged in case there were some merged cells. Still get the same error message. Am I referring to the right sheet in the code????

Have you tried qualifying the ranges as per post #3?

 

[GTO]

Warning: "air code"

Not tested, but regardless of whether there is an unknown merged cell, you cannot go wrong in being more explicit as Fluff suggests:

Option Explicit
  
Private Sub Worksheet_Activate()
  
  Application.ScreenUpdating = False
  
  'Sheets("Resurstid").Visible = True
  'Work against this workbook (the one with the code) and specifically the worksheet 'Resurstid'
  With ThisWorkbook.Worksheets("Resurstid")
    
    .Visible = xlSheetVisible
    'This way, everything with a stop before it, as Fluff advised, is specific to this worksheet...
    'regardless of what worksheet or even which workbook is active/has focus.
    .Range("A:F").AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=.Range("H1:M2"), _
                                         CopyToRange:=.Range("H4"), _
                                         Unique:=True
    
    .Visible = xlSheetHidden
  End With
  'Sheets("Resurstid").Visible = False
  
  Application.ScreenUpdating = True
  Sheets("Analys per konsult").Select
   
End Sub

Hope that helps,

Mark

 

[Alex B]

Warning: "air code"

Not tested, but regardless of whether there is an unknown merged cell, you cannot go wrong in being more explicit as Fluff suggests:

Option Explicit
  
Private Sub Worksheet_Activate()
  
  Application.ScreenUpdating = False
  
  'Sheets("Resurstid").Visible = True
  'Work against this workbook (the one with the code) and specifically the worksheet 'Resurstid'
  With ThisWorkbook.Worksheets("Resurstid")
    
    .Visible = xlSheetVisible
    'This way, everything with a stop before it, as Fluff advised, is specific to this worksheet...
    'regardless of what worksheet or even which workbook is active/has focus.
    .Range("A:F").AdvancedFilter Action:=xlFilterCopy, _
                                         CriteriaRange:=.Range("H1:M2"), _
                                         CopyToRange:=.Range("H4"), _
                                         Unique:=True
    
    .Visible = xlSheetHidden
  End With
  'Sheets("Resurstid").Visible = False
  
  Application.ScreenUpdating = True
  Sheets("Analys per konsult").Select
   
End Sub

Hope that helps,

Mark

Mark, perfect!!

Thanks for your efforts, guys!!!