Solved

Reference by column name instead of an absolute number

Posted on 2013-12-05
2
332 Views
Last Modified: 2013-12-24
Hi, I have a working macro that performs the following:
1. Selects all the fields in a table in the column "Functional Area" besides the  function  that was selected on the cover sheet (aka. Series_Name);
2. Deletes all those fields and their data in the table;
3. Then un-filters (shows) only the selected function

It is running fine when I refer to the column number of Initiative Summary tab (number 3). However, when I'm trying  to reference by name instead of referring to absolute column numbers, the macro doesn't work.
o      Instead of:
¿      Sheets("Initiative Summary").ListObjects("InitiativeTbl").Range.AutoFilter Field:=3
o      I try:
¿      Sheets("Initiative Summary").ListObjects("InitiativeTbl").Range.AutoFilter Field:=.ListColumns("Functional Area").Index

Could you please help me figure out how to make it work?

Sub Macro3()
'
' Macro3 Macro
'
Dim Series_Name As String

   Series_Name = Range("Cover!$C$8").Value
   
  If Series_Name <> "All" Then
   
     Sheets("Initiative Summary").ListObjects("InitiativeTbl").Range.AutoFilter Field:=3
   
     With Sheets("Initiative Summary").ListObjects("InitiativeTbl")
   
        .Range.AutoFilter Field:=.ListColumns("Functional Area").Index, Criteria1:=("<>" & Series_Name), Operator:=xlFilterValues
   
     End With
   
     Sheets("Initiative Summary").ListObjects("InitiativeTbl").ListColumns("Functional Area").DataBodyRange.SpecialCells(xlCellTypeVisible).Delete
   
     With Sheets("Initiative Summary").ListObjects("InitiativeTbl")
        .Range.AutoFilter Field:=3
     End With
     
   Else
      Sheets("Initiative Summary").ListObjects("InitiativeTbl").Range.AutoFilter Field:=3

End If

End Sub
0
Comment
Question by:jmac001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39699023
Post the file here, okay?
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 39700495
Does the user have to select the column name in some way?

If so, create a variable from that selection and use that variable in a MATCH function to generate the column number:

Application.WorksheetFunction.Match(Variable,Range,MatchType)

Thanks
Rob H
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question