Powershell: Autofilter based on a column value
I have seen several post on this but they all seem to use the same example so I am not sure how the code would differ from scenario to scenario:
Here is what I have:
$rng = $sheet.Cells.Item(1,7).EntireColumn #choose all columns from 1 to 7
$rng.select | Out-Null # select this range
$filterValue = @("TRUE") # create filter value, that is only choose when value matches "TRUE"
$xlFilterValues = 7 # "found in MS documentation", copied not sure what this does
$objExcel.Selection.AutoFilter(7,$filterValue, $xlFilterValues) # here the first parameter value of 7 is the column I am trying to filter on
$rowMax = ($sheet.FilteredRange.Rows).count
$colMax = ($sheet.FilteredRange.Columns).count
Although my script doesn't crash or anything, but looks like the filter isn't working. I have around thousand rows, where the filtering columns have values set to "TRUE", "FALSE" or Blank.
My expectation is:
will output 7 for $colMax and 40 (number of rows where column 7 has a value of "TRUE") for $rowMax.
However, I am getting 0 for $rowMax.
excel powershell filter
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
|
show 1 more comment
I have seen several post on this but they all seem to use the same example so I am not sure how the code would differ from scenario to scenario:
Here is what I have:
$rng = $sheet.Cells.Item(1,7).EntireColumn #choose all columns from 1 to 7
$rng.select | Out-Null # select this range
$filterValue = @("TRUE") # create filter value, that is only choose when value matches "TRUE"
$xlFilterValues = 7 # "found in MS documentation", copied not sure what this does
$objExcel.Selection.AutoFilter(7,$filterValue, $xlFilterValues) # here the first parameter value of 7 is the column I am trying to filter on
$rowMax = ($sheet.FilteredRange.Rows).count
$colMax = ($sheet.FilteredRange.Columns).count
Although my script doesn't crash or anything, but looks like the filter isn't working. I have around thousand rows, where the filtering columns have values set to "TRUE", "FALSE" or Blank.
My expectation is:
will output 7 for $colMax and 40 (number of rows where column 7 has a value of "TRUE") for $rowMax.
However, I am getting 0 for $rowMax.
excel powershell filter
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
Why do you have@("TRUE")as the filter? Thats making the object a single item array
– trebleCode
Nov 20 at 21:36
@trebleCode Yes I am aware of that, but it should still work right?
– Shamim Hafiz
Nov 20 at 21:45
The autofiltering piece certainly works, where are you getting the rest of that code where you're usingFilteredRange.Rows, etc.?
– trebleCode
Nov 20 at 22:01
@trebleCode The whole code snippet is actually together. I have edited my post to reflect this. The code snippet before that opens the file and gets WSheet and those I believe are fine as I can see the rows. Do you want to anything specific?
– Shamim Hafiz
Nov 20 at 22:15
For$rowMaxyou writeFilteredRange.Rows(with a dot in between). For$colMaxyou useFilteredRangeColumns(no dot).
– Theo
Nov 21 at 9:47
|
show 1 more comment
I have seen several post on this but they all seem to use the same example so I am not sure how the code would differ from scenario to scenario:
Here is what I have:
$rng = $sheet.Cells.Item(1,7).EntireColumn #choose all columns from 1 to 7
$rng.select | Out-Null # select this range
$filterValue = @("TRUE") # create filter value, that is only choose when value matches "TRUE"
$xlFilterValues = 7 # "found in MS documentation", copied not sure what this does
$objExcel.Selection.AutoFilter(7,$filterValue, $xlFilterValues) # here the first parameter value of 7 is the column I am trying to filter on
$rowMax = ($sheet.FilteredRange.Rows).count
$colMax = ($sheet.FilteredRange.Columns).count
Although my script doesn't crash or anything, but looks like the filter isn't working. I have around thousand rows, where the filtering columns have values set to "TRUE", "FALSE" or Blank.
My expectation is:
will output 7 for $colMax and 40 (number of rows where column 7 has a value of "TRUE") for $rowMax.
However, I am getting 0 for $rowMax.
excel powershell filter
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
I have seen several post on this but they all seem to use the same example so I am not sure how the code would differ from scenario to scenario:
Here is what I have:
$rng = $sheet.Cells.Item(1,7).EntireColumn #choose all columns from 1 to 7
$rng.select | Out-Null # select this range
$filterValue = @("TRUE") # create filter value, that is only choose when value matches "TRUE"
$xlFilterValues = 7 # "found in MS documentation", copied not sure what this does
$objExcel.Selection.AutoFilter(7,$filterValue, $xlFilterValues) # here the first parameter value of 7 is the column I am trying to filter on
$rowMax = ($sheet.FilteredRange.Rows).count
$colMax = ($sheet.FilteredRange.Columns).count
Although my script doesn't crash or anything, but looks like the filter isn't working. I have around thousand rows, where the filtering columns have values set to "TRUE", "FALSE" or Blank.
My expectation is:
will output 7 for $colMax and 40 (number of rows where column 7 has a value of "TRUE") for $rowMax.
However, I am getting 0 for $rowMax.
excel powershell filter
excel powershell filter
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
edited Nov 21 at 19:32
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
asked Nov 20 at 20:00
Shamim Hafiz
12k2988151
12k2988151
Why do you have@("TRUE")as the filter? Thats making the object a single item array
– trebleCode
Nov 20 at 21:36
@trebleCode Yes I am aware of that, but it should still work right?
– Shamim Hafiz
Nov 20 at 21:45
The autofiltering piece certainly works, where are you getting the rest of that code where you're usingFilteredRange.Rows, etc.?
– trebleCode
Nov 20 at 22:01
@trebleCode The whole code snippet is actually together. I have edited my post to reflect this. The code snippet before that opens the file and gets WSheet and those I believe are fine as I can see the rows. Do you want to anything specific?
– Shamim Hafiz
Nov 20 at 22:15
For$rowMaxyou writeFilteredRange.Rows(with a dot in between). For$colMaxyou useFilteredRangeColumns(no dot).
– Theo
Nov 21 at 9:47
|
show 1 more comment
Why do you have@("TRUE")as the filter? Thats making the object a single item array
– trebleCode
Nov 20 at 21:36
@trebleCode Yes I am aware of that, but it should still work right?
– Shamim Hafiz
Nov 20 at 21:45
The autofiltering piece certainly works, where are you getting the rest of that code where you're usingFilteredRange.Rows, etc.?
– trebleCode
Nov 20 at 22:01
@trebleCode The whole code snippet is actually together. I have edited my post to reflect this. The code snippet before that opens the file and gets WSheet and those I believe are fine as I can see the rows. Do you want to anything specific?
– Shamim Hafiz
Nov 20 at 22:15
For$rowMaxyou writeFilteredRange.Rows(with a dot in between). For$colMaxyou useFilteredRangeColumns(no dot).
– Theo
Nov 21 at 9:47
Why do you have
@("TRUE") as the filter? Thats making the object a single item array– trebleCode
Nov 20 at 21:36
Why do you have
@("TRUE") as the filter? Thats making the object a single item array– trebleCode
Nov 20 at 21:36
@trebleCode Yes I am aware of that, but it should still work right?
– Shamim Hafiz
Nov 20 at 21:45
@trebleCode Yes I am aware of that, but it should still work right?
– Shamim Hafiz
Nov 20 at 21:45
The autofiltering piece certainly works, where are you getting the rest of that code where you're using
FilteredRange.Rows, etc.?– trebleCode
Nov 20 at 22:01
The autofiltering piece certainly works, where are you getting the rest of that code where you're using
FilteredRange.Rows, etc.?– trebleCode
Nov 20 at 22:01
@trebleCode The whole code snippet is actually together. I have edited my post to reflect this. The code snippet before that opens the file and gets WSheet and those I believe are fine as I can see the rows. Do you want to anything specific?
– Shamim Hafiz
Nov 20 at 22:15
@trebleCode The whole code snippet is actually together. I have edited my post to reflect this. The code snippet before that opens the file and gets WSheet and those I believe are fine as I can see the rows. Do you want to anything specific?
– Shamim Hafiz
Nov 20 at 22:15
For
$rowMax you write FilteredRange.Rows (with a dot in between). For $colMax you use FilteredRangeColumns (no dot).– Theo
Nov 21 at 9:47
For
$rowMax you write FilteredRange.Rows (with a dot in between). For $colMax you use FilteredRangeColumns (no dot).– Theo
Nov 21 at 9:47
|
show 1 more comment
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53400671%2fpowershell-autofilter-based-on-a-column-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53400671%2fpowershell-autofilter-based-on-a-column-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Why do you have
@("TRUE")as the filter? Thats making the object a single item array– trebleCode
Nov 20 at 21:36
@trebleCode Yes I am aware of that, but it should still work right?
– Shamim Hafiz
Nov 20 at 21:45
The autofiltering piece certainly works, where are you getting the rest of that code where you're using
FilteredRange.Rows, etc.?– trebleCode
Nov 20 at 22:01
@trebleCode The whole code snippet is actually together. I have edited my post to reflect this. The code snippet before that opens the file and gets WSheet and those I believe are fine as I can see the rows. Do you want to anything specific?
– Shamim Hafiz
Nov 20 at 22:15
For
$rowMaxyou writeFilteredRange.Rows(with a dot in between). For$colMaxyou useFilteredRangeColumns(no dot).– Theo
Nov 21 at 9:47