CODEWITHSUNDEEP
excelvba
DSM
DSM

Reputation: 257

Working with merge cells to filter data in a worksheet

I am trying to filter some data using row 4 as the column headers. The problem here is that I have some cells that are merged between row 2 and 3. When I try filtering for row 4, the VBA code filters row 2 instead of row 3. I was recording a few steps of turning on the filter for Row 3, and then selecting a few criteria to filter for in some of the columns. This is the code which was produced by the excel VBA recorder.

code:

Sub Macro1()
    Rows("3:3").Select
    Range("D3").Activate
    Selection.AutoFilter
    ActiveSheet.Range("$A$3:$G$8").AutoFilter Field:=1, Criteria1:=Array("1", _
        "3", "4"), Operator:=xlFilterValues
    ActiveSheet.Range("$A$3:$G$8").AutoFilter Field:=7, Criteria1:="M"
End Sub

How this code was produced and how was the data structured: I prepared a simple worksheet which more or less faces the same difficulty. The data is structured in the same way as captured in the following screenshot.

enter image description here

All I did was select row 3, turn on the filter mode, and did some filtering in the columns. That is how the vba code was produced.

enter image description here

However, when I run the same VBA code that was produced against the data, I get this as the results.

enter image description here

We can see that it is taking row 2 as the header instead of row 3. What is the problem here?

Upvotes: 1

Views: 674

Answers (2)

Damian
Damian

Reputation: 5174

It happens because when you tell the macro to select row 3 is selecting rows 2 and 3.

Check it out, debug your code using F8 and after the Rows("3:3").Select you will see both rows selected.

To avoid this and so many errors you shouldn't use .Select or .Activate

Here is how:

Option Explicit
Sub Macro1()

    With ThisWorkbook.Sheets("Datos") 'change the name to your working sheet
        If .AutoFilterMode = True Then .AutoFilterMode = False 'first check that there are no filters
        .Range("$A$3:$G$8").AutoFilter Field:=2, Criteria1:=Array("1", _
        "3", "4"), Operator:=xlFilterValues
        .Range("$A$3:$G$8").AutoFilter Field:=7, Criteria1:="M"
    End With

End Sub

Also while we are at it, using Option Explicit will force you to declare variables to avoid errors. And an extended answer on how to avoid using select.

Upvotes: 1

LOKE2707
LOKE2707

Reputation: 312

You need not use a selection of rows separately.

Sub Macro1()

ActiveSheet.Range("$A$3:$G$8").AutoFilter Field:=1, Criteria1:=Array("1", _
    "3", "4"), Operator:=xlOr
ActiveSheet.Range("$A$3:$G$8").AutoFilter Field:=7, Criteria1:="M"

End Sub

Upvotes: 1

Related Questions