By using Macro in the Column E - Excel

June 22, 2010 at 01:58:09
Specs: Windows XP
I am using macro for filtering items in E column, now i need to add some column before E somewhere. Filtering columns has been moved to F now macro is not working. How can i resolve this problem without making again macro. Suggesstion pls

See More: By using Macro in the Column E - Excel

Report •

June 22, 2010 at 04:43:16

The simple answer is to edit the macro to point to the new column. You don't have to write a whole new macro.

Alternatively if the filter column has a unique header on row 1 then this macro will find it and apply the filter:

Sub FiltTest()
Dim rngFilter As Range
Dim strFilterName As String

'set unique filter column header text
'change as appropriate
strFilterName = "Filter Data"

With Worksheets("Sheet1")
    'find column to filter (in row 1)
    Set rngFilter = .Range("1:1").Find(strFilterName, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
    If Not rngFilter Is Nothing Then
        'filter column header found - use its column for the filter
        With rngFilter.EntireColumn
            'change the filter criteria to match
            'or use a reference to a cell containing the filter criteria
            .AutoFilter Field:=1, Criteria1:="B"
        End With
        'filter heading name not found - display warning message
        MsgBox "Your filter column header: " & vbCrLf & _
                strFilterName & vbCrLf & _
                "was not found"
    End If
End With
End Sub

You should be able to use the find part of this code with your existing macro.

You can now add or remove columns and the filter will still work.
Change the unique column header name on this line:

'set unique filter column header text
'change as appropriate
strFilterName = "Filter Data"


Report •

June 22, 2010 at 04:48:31
It would have helped if you had posted the code, but I'll toss out a couple of suggestions.

Somewhere in the code I assume there is one or more references to Columns("E") or Columns(5) or a range that includes cells in Column E e.g. "E1:E100".

You need to change any and all of those references to "F" or 6 or "F1:F100" or whatever "fixes" the code.

Another option, which would make the code more flexible, would be to have it "find" the column you want it to filter. If you use a column heading or some other keyword for the code to search for, it can use the column in which it finds the keyword as the column to filter. With that method in place, you could add and delete columns all day and the code would still filter the column you need it to.

We would need to see the code (and know the keyword) in order to provide any more details.

Report •

Related Solutions

Ask Question