How to auto select range from a filter without having to manually enter it?

Question

I am wondering if there is a way to auto select the cell range based on selected filter.

Example:

Set Filter in (Column H)
Auto select the result cell data starting Column A (A2000): Column C (C5000) etc. --- This is where I am getting stuck. I do not know how to write to auto select the result cell data based on the selected filter.

Currently, my workout is to manually enter the cell so I could move on with writing the codes. I hope I am making sense above.

---- Code----

function ColHtoActive() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Current');  
  var crange = sheet.getRange('A7:I7350').activate();                                
  var currentCell = sheet.setCurrentCell(sheet.getRange('H7'); 
  var hSfilter = sheet.getRange('A7:I7350').createFilter(); 
  var ccC1 = sheet.getRange('H7').activate();
  var cCriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues('Inactive']).build(); 

  sheet.getFilter().setColumnFilterCriteria(8, cCriteria);    
}

function copycolA() {
  var ss = SpreadsheetApp.getActive().getSheetByName('A');
  ss.getRange('A2307').activate();
  ss.getRange('A2307:A7155').copyTo(
      ss.getActiveRange(),
      SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
      false);
}

What kind of filter have you set? And you want the filtered range? — TheMaster
I have added sample screen shot and edited the code. I have pasted the wrong code earlier. The code above after H7 has been activated and filter is set to "Active", I would like the code to be able to select the range automatically without manually entering the range. My next function is to copy the data into another tab based from the Active result range. I used "ss.getRange('A2307').activate(); ss.getRange('A2307:C7155').copyTo(ss.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);" Is there a way for the code to auto select "A2307:C7155" without manually entering it? — JMMR...
How do you get the number 2307 and 7155, without manually entering ? In your current question, I cannot see whatever I can refer to to get these 2307 and 7155 out. What's the meaning of 2307 and 7155 ? Are they some random number ? In the picture you provided, if I select "Active" at H2, which range should be selected, and why ? if I select "Active" at H242, which range should be selected, and why ? — HaPhan
('A2307:A7155') is the result of the range after you set the filter to Active. For the provide sample this would be (A23:A2288). What I am noticing now is that when it get copies to another sheet, it appears that it just copies and pastes the entire data from A23:A2288 (Filter is set only for Active rows but it also includes Inactive rows). What I would like (or hoping) to the code to do is after H2 is filtered to Active, select the result A23: A2288 (A23:B2288...or whatever range) automatically then copy/paste the value into another tab. — JMMR...

TheMaster TheMaster · Accepted Answer · 2018-09-07T17:39:13

You can get the filtered range dimensions from getFilter().getRange(). This will copy all the filtered range:

function copycolA() {
  var sourceSheet = SpreadsheetApp.getActive().getSheetByName('Current');
  var targetSheet = SpreadsheetApp.getActive().getSheetByName('A');
  var sourceRange = sourceSheet.getFilter().getRange();
  sourceRange.copyTo(
    targetSheet.getRange('A1'),
    SpreadsheetApp.CopyPasteType.PASTE_NORMAL,
    false);
}

How to auto select range from a filter without having to manually enter it?

1 Answers

To read: