Excel VBA Referencing Col Number from Named Range
Results 1 to 2 of 2

Thread: Excel VBA Referencing Col Number from Named Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA Referencing Col Number from Named Range

    Hi, in the code below I am trying to reference the column number within setColumnFilterCriteria from the named range AttendeeStatus so that this piece works no matter where this column is. Any ideas on how to do so?

    Code:
    function FilterAttendees() {  var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('AttendeeStatus').activate();
      spreadsheet.getRange('AttendeeStatus').createFilter();
      spreadsheet.getRange('AttendeeStatus').activate();
      var criteria = SpreadsheetApp.newFilterCriteria()
      .setHiddenValues(['', 'Attending'])
      .build();
     
      spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(, criteria);
      spreadsheet.getActiveSheet().setName('Registrants');
    
    
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.insertSheet(1);
      spreadsheet.getCurrentCell().activate();
      spreadsheet.getActiveSheet().setName('Attendees');
      spreadsheet.getCurrentCell().activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Registrants'), true);
      var sheet = spreadsheet.getActiveSheet();
      sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Attendees'), true);
      spreadsheet.getRange('Registrants!A1:Z500').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
      
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('AttendeeStatus').activate();
      spreadsheet.getActiveSheet().getFilter().remove();
      
      var spreadsheet = SpreadsheetApp.getActive();
      spreadsheet.getRange('1:1').activate();
      spreadsheet.getActiveRangeList().setFontWeight('bold');
      spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Attendees'), true);
      spreadsheet.getRange('1:1').activate();
      spreadsheet.getActiveRangeList().setFontWeight('bold');
    };

  2. #2
    Board Regular Leith Ross's Avatar
    Join Date
    Mar 2008
    Location
    San Francisco, CA
    Posts
    1,780
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA Referencing Col Number from Named Range

    Hello P9807,

    Welcome!

    You do realize you posted Google Apps Script code in a VBA forum, yes?
    Sincerely,
    Leith Ross

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •