You’re offline. This is a read only version of the page.
Good Day,
a helpful voice in the general Excel help forum pointed me to ask my question here, I'll post the original topic here then explain what else I've tried so far.
I'm not a programmer, I have very few programming skills and while I managed one of the functions from VBA that my company needs in Excel Web / Office Script, the second function eludes me.
What I'm trying to do is sort a large table by clicking a button after I've selected a cell.
For example, I click in Column H the number "A53434", when I click the button, it should sort the table so only entries that have "A53434" in Column H. I feel like this shouldn't be so hard but I'm struggling quite a lot.
Here is the code a previous employee did in Excel VBA:
Sub SetFilter()
With ActiveSheet
.Range("A3:R3").AutoFilter
.Range("A3:R" & .Cells(Rows.Count, "A").End(xlUp).Row).AutoFilter _
Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End With
End Sub
So far I've gotten this far in Office Script, but I have very little idea what I'm doing or what I'm doing wrong:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
let activeCell = workbook.getActiveCell().getValue() as string;
console.log(activeCell);
//just to see if I actually have the active cell
let activeColumn = workbook.getActiveCell().getAddress().split('')[0];
let table = selectedSheet.getTables()[0];
//above line gives error
var sortCl = table.getColumnByName(activeColumn);
}
Please help a struggling non-programmer out, thanks.
After a bit back and forth I've gotten this far and I think I understand it
Share
Reportfunction main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
let activeCell = workbook.getActiveCell();
let activeColumn = activeCell.getAddress().split('')[10];
//this gives me the Columns letter as the full Adress is a 9 letter word and ! then the column index
let activeColumnINdex = activeCell.getColumnIndex();
//this gives me the columns Index as a numerical value, which one I need? No idea.
console.log(activeColumn)
let table = selectedSheet.getTables()[0];
let filter = table.getAutoFilter();
filter.clearCriteria();
filter.apply(selectedSheet.getAutoFilter().getRange(),activeColumnINdex, filter);
// I think I understand the apply() function, first I give it a range (all of it) then the Column Index (A=0, B=1 etc.) but then I try to give it a criteria, which ought to be the currently active cell and I don't know how to do that.
}
Good morning Sandeep,
thank you for your reply, I think this is going in the right direction, however it still gives an error message in line 19 that "criteria" data type cannot be inferred. Furthermore it underlines AutoFilterCriteria as the namespace ExcelScript has no member of that name.
€dit: Okay, I changed a little bit of your proposed solution and it works now, I changed:
let criteria: ExcelScript.AutoFilterCriteria =
to
let criteria: ExcelScript.FilterCriteria =
And
filter.apply(table.getRange(), activeColumnIndex + 1, criteria); // Column index is 1-based in apply function
to
filter.apply(table.getRange(), activeColumnIndex, criteria); // Column index is 1-based in apply function
Hi There,
You can use this function -
function main(workbook: ExcelScript.Workbook) {
// Get the active worksheet
let selectedSheet = workbook.getActiveWorksheet();
// Get the active cell and its value
let activeCell = workbook.getActiveCell();
let activeCellValue = activeCell.getValue() as string;
// Get the active cell column index
let activeColumnIndex = activeCell.getColumnIndex();
// Get the first table on the sheet (assuming there is one and it is the correct one)
let table = selectedSheet.getTables()[0];
// Apply filter to the active column with the value of the active cell
let filter = table.getAutoFilter();
filter.clearCriteria(); // Clear any existing criteria
// Create the filter criteria based on the active cell's value
let criteria: ExcelScript.AutoFilterCriteria = {
filterOn: ExcelScript.FilterOn.values,
values: [activeCellValue]
};
// Apply the criteria to the active column
filter.apply(table.getRange(), activeColumnIndex + 1, criteria); // Column index is 1-based in apply function
}
Please give kudos and mark as solution if it helps.
Thanks,
Sandeep Mishra
Good morning Nived,
I wasn't sure either but since the Excel forum pointed me here...
Thank you for your solution, however the script doesn't work - the error it gives out is that no data type of any of the "let ..." can be infered. Furthermore I don't want to filter by a specific key word, I want to dynamically get whatever cell is selected to be searched for - i.e. I click Column H, Row 64530 which contains the word "Street Name 1", and if I hit the filter key, it filters my whole table for Column H entries containing "Street Name 1", if I click another cell and then hit the filter, it should clear all current filters, then search for whatever the new cell contains.
The background is a large table full of customer data and electrical solar power connectors, it's a help database since the geo information system has an updating time, meaning if in the past few months a lot of solar power was put into a region, the geo information system does not have that information yet, we use an excel table to keep track of these changes (aside from a grid database that is always up to date but is hard to search in).
A big part of this excel table are old VBA scripts that allow us to instantly filter by electrical sub stations, since there are a lot of switching around, the normal filter function of excel is cumbersome and sometimes we have multiple dozens of new solar panels a day. One button filtering is very helpful.
Not sure whether this is a power automate specific question, but for your question, could you try this office script:
All responses (
Answers (function sortTableByColumnHValue() {
// Get the table range
let table = worksheet.getTables().getItems()[0];
let tableRange = table.getRange();
// Get the data range of the table
let dataRange = tableRange.getRangeBetweenHeaderAndTotal();
// Get the column index for Column H
let headerRange = tableRange.getHeaderRowRange();
let columnIndex = headerRange.getValues()[0].indexOf("Column H Header") + 1; // Replace "Column H Header" with your actual header name
// Get the value to filter/sort
let filterValue = "A53434"; // Replace this with the value you want to filter by
// Filter the table for rows containing the specific value in Column H
let filteredData = dataRange.getValues().filter(row => row[columnIndex - 1] === filterValue);
// Update the table range with the filtered data
dataRange.setValues(filteredData);
// Sort the table by the filtered column (if needed)
tableRange.sort.apply([{ key: columnIndex, ascending: true }]);
}
Nived N 🚀
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
🔍 Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you! 🙌
#1
WarrenBelz
146,743
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,079
Most Valuable Professional
