How to Count rows without Header After filtering the table using the VBA code?
I have 2 sheets in excel file with two tables:
Sheet 1:
Having the table of the data to be filtered
Sheet 2:
Having the summary page that shows the number of Rows to a specific filter.
It have a filter control panel
I have created a code on the VB and assign it to a button to be clickable and my code to filter the table is:
Sheets(ItemsSheet).Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:="=Menu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3, Criteria1:="=Submenu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=Phase_Column, Criteria1:= _
Array(Phase, "="), Operator:=xlFilterValues
When I Execute this code, it redirect me to the Sheet 1 and the filter is applied successfully.
Question:
- How Can I calculate the number of rows located in the [Sheet1]?
- How to display the number of rows in a cell located in the [Sheet2] after filter?
- How to execute the above code without redirect to the [Sheet1]?
Image for your reference:
excel vba excel-vba excel-formula
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
asked Nov 12 at 13:53
SDiab
265
add a comment |
I have 2 sheets in excel file with two tables:
Sheet 1:
Having the table of the data to be filtered
Sheet 2:
Having the summary page that shows the number of Rows to a specific filter.
It have a filter control panel
I have created a code on the VB and assign it to a button to be clickable and my code to filter the table is:
Sheets(ItemsSheet).Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:="=Menu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3, Criteria1:="=Submenu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=Phase_Column, Criteria1:= _
Array(Phase, "="), Operator:=xlFilterValues
When I Execute this code, it redirect me to the Sheet 1 and the filter is applied successfully.
Question:
- How Can I calculate the number of rows located in the [Sheet1]?
- How to display the number of rows in a cell located in the [Sheet2] after filter?
- How to execute the above code without redirect to the [Sheet1]?
Image for your reference:
excel vba excel-vba excel-formula
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
asked Nov 12 at 13:53
SDiab
265
If all you want to know is how many rows you could use COUNTIFS rather than vb code
– Harassed Dad
Nov 12 at 14:09
I have large number of criteria so it works with me to do it in a VBA code, I agree that countifs is much faster but for reliable code I used to do it as a VBA Code.
– SDiab
Nov 12 at 14:12
In this case I can use the cell in both ways with a single code, pasting the number of rows for a specific filter and a button that can take me directly to the filtered page.
– SDiab
Nov 12 at 14:16
Aggregatecan count number of visible cells (by ignoring blanks
– urdearboy
Nov 12 at 14:31
Thanks for helping it works now :). Any help regarding the Auto filter without redirecting to the filtered sheet?
– SDiab
Nov 12 at 14:47
add a comment |
I have 2 sheets in excel file with two tables:
Sheet 1:
Having the table of the data to be filtered
Sheet 2:
Having the summary page that shows the number of Rows to a specific filter.
It have a filter control panel
I have created a code on the VB and assign it to a button to be clickable and my code to filter the table is:
Sheets(ItemsSheet).Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:="=Menu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3, Criteria1:="=Submenu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=Phase_Column, Criteria1:= _
Array(Phase, "="), Operator:=xlFilterValues
When I Execute this code, it redirect me to the Sheet 1 and the filter is applied successfully.
Question:
- How Can I calculate the number of rows located in the [Sheet1]?
- How to display the number of rows in a cell located in the [Sheet2] after filter?
- How to execute the above code without redirect to the [Sheet1]?
Image for your reference:
excel vba excel-vba excel-formula
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
asked Nov 12 at 13:53
SDiab
265
I have 2 sheets in excel file with two tables:
Sheet 1:
Having the table of the data to be filtered
Sheet 2:
Having the summary page that shows the number of Rows to a specific filter.
It have a filter control panel
I have created a code on the VB and assign it to a button to be clickable and my code to filter the table is:
Sheets(ItemsSheet).Select
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=2, Criteria1:="=Menu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=3, Criteria1:="=Submenu"
ActiveSheet.ListObjects("Table2").Range.AutoFilter Field:=Phase_Column, Criteria1:= _
Array(Phase, "="), Operator:=xlFilterValues
When I Execute this code, it redirect me to the Sheet 1 and the filter is applied successfully.
Question:
- How Can I calculate the number of rows located in the [Sheet1]?
- How to display the number of rows in a cell located in the [Sheet2] after filter?
- How to execute the above code without redirect to the [Sheet1]?
Image for your reference:
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
asked Nov 12 at 13:53
SDiab
265
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
asked Nov 12 at 13:53
SDiab
265
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
edited Nov 12 at 14:05
Pᴇʜ
20.1k42650
20.1k42650
asked Nov 12 at 13:53
SDiab
265
asked Nov 12 at 13:53
SDiab
265
asked Nov 12 at 13:53
SDiab
265
265
If all you want to know is how many rows you could use COUNTIFS rather than vb code
– Harassed Dad
Nov 12 at 14:09
I have large number of criteria so it works with me to do it in a VBA code, I agree that countifs is much faster but for reliable code I used to do it as a VBA Code.
– SDiab
Nov 12 at 14:12
In this case I can use the cell in both ways with a single code, pasting the number of rows for a specific filter and a button that can take me directly to the filtered page.
– SDiab
Nov 12 at 14:16
Aggregatecan count number of visible cells (by ignoring blanks
– urdearboy
Nov 12 at 14:31
Thanks for helping it works now :). Any help regarding the Auto filter without redirecting to the filtered sheet?
– SDiab
Nov 12 at 14:47
add a comment |
If all you want to know is how many rows you could use COUNTIFS rather than vb code
– Harassed Dad
Nov 12 at 14:09
I have large number of criteria so it works with me to do it in a VBA code, I agree that countifs is much faster but for reliable code I used to do it as a VBA Code.
– SDiab
Nov 12 at 14:12
In this case I can use the cell in both ways with a single code, pasting the number of rows for a specific filter and a button that can take me directly to the filtered page.
– SDiab
Nov 12 at 14:16
Aggregatecan count number of visible cells (by ignoring blanks
– urdearboy
Nov 12 at 14:31
Thanks for helping it works now :). Any help regarding the Auto filter without redirecting to the filtered sheet?
– SDiab
Nov 12 at 14:47
If all you want to know is how many rows you could use COUNTIFS rather than vb code
– Harassed Dad
Nov 12 at 14:09
If all you want to know is how many rows you could use COUNTIFS rather than vb code
– Harassed Dad
Nov 12 at 14:09
I have large number of criteria so it works with me to do it in a VBA code, I agree that countifs is much faster but for reliable code I used to do it as a VBA Code.
– SDiab
Nov 12 at 14:12
I have large number of criteria so it works with me to do it in a VBA code, I agree that countifs is much faster but for reliable code I used to do it as a VBA Code.
– SDiab
Nov 12 at 14:12
In this case I can use the cell in both ways with a single code, pasting the number of rows for a specific filter and a button that can take me directly to the filtered page.
– SDiab
Nov 12 at 14:16
In this case I can use the cell in both ways with a single code, pasting the number of rows for a specific filter and a button that can take me directly to the filtered page.
– SDiab
Nov 12 at 14:16
Aggregate can count number of visible cells (by ignoring blanks– urdearboy
Nov 12 at 14:31
Aggregate can count number of visible cells (by ignoring blanks– urdearboy
Nov 12 at 14:31
Thanks for helping it works now :). Any help regarding the Auto filter without redirecting to the filtered sheet?
– SDiab
Nov 12 at 14:47
Thanks for helping it works now :). Any help regarding the Auto filter without redirecting to the filtered sheet?
– SDiab
Nov 12 at 14:47
add a comment |
1 Answer
1
active
oldest
votes
After I did many Searches on this Question. I have find a solution for that:
Add the below Code after executing the Filter Code:
Code:
Dim mycount As Long
mycount = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible).Count - 1
MsgBox mycount
Result:
You will get all rows that are visible after applying the filter.
Regards
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
answered Nov 12 at 14:31
SDiab
265
add a comment |
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%2f53263651%2fhow-to-count-rows-without-header-after-filtering-the-table-using-the-vba-code%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
After I did many Searches on this Question. I have find a solution for that:
Add the below Code after executing the Filter Code:
Code:
Dim mycount As Long
mycount = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible).Count - 1
MsgBox mycount
Result:
You will get all rows that are visible after applying the filter.
Regards
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
answered Nov 12 at 14:31
SDiab
265
add a comment |
After I did many Searches on this Question. I have find a solution for that:
Add the below Code after executing the Filter Code:
Code:
Dim mycount As Long
mycount = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible).Count - 1
MsgBox mycount
Result:
You will get all rows that are visible after applying the filter.
Regards
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
answered Nov 12 at 14:31
SDiab
265
add a comment |
After I did many Searches on this Question. I have find a solution for that:
Add the below Code after executing the Filter Code:
Code:
Dim mycount As Long
mycount = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible).Count - 1
MsgBox mycount
Result:
You will get all rows that are visible after applying the filter.
Regards
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
answered Nov 12 at 14:31
SDiab
265
After I did many Searches on this Question. I have find a solution for that:
Add the below Code after executing the Filter Code:
Code:
Dim mycount As Long
mycount = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible).Count - 1
MsgBox mycount
Result:
You will get all rows that are visible after applying the filter.
Regards
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
answered Nov 12 at 14:31
SDiab
265
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
edited Nov 12 at 16:29
Glitch_Doctor
2,31621027
2,31621027
answered Nov 12 at 14:31
SDiab
265
answered Nov 12 at 14:31
SDiab
265
answered Nov 12 at 14:31
SDiab
265
265
add a comment |
add a comment |
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%2f53263651%2fhow-to-count-rows-without-header-after-filtering-the-table-using-the-vba-code%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
If all you want to know is how many rows you could use COUNTIFS rather than vb code
– Harassed Dad
Nov 12 at 14:09
I have large number of criteria so it works with me to do it in a VBA code, I agree that countifs is much faster but for reliable code I used to do it as a VBA Code.
– SDiab
Nov 12 at 14:12
In this case I can use the cell in both ways with a single code, pasting the number of rows for a specific filter and a button that can take me directly to the filtered page.
– SDiab
Nov 12 at 14:16
Aggregatecan count number of visible cells (by ignoring blanks– urdearboy
Nov 12 at 14:31
Thanks for helping it works now :). Any help regarding the Auto filter without redirecting to the filtered sheet?
– SDiab
Nov 12 at 14:47