• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro error after Macro runs (successfully)

O

oldmaninla

New Member
I have a simple macro that runs fine (hides zero rows) but at the end of the macro I get an error "macro run-time error '13' type mismatch".
Here is my macro:
>>> use code - tags <<<
Code: 
    Sub HideZeroRows()
   Dim M As Long, LastRow As Long
   Dim ws As Worksheet

   For Each ws In ActiveWorkbook.Worksheets
       LastRow = ws.Range("U400").End(xlUp).Row
       For M = LastRow To 7 Step -1
               If ws.Range("U" & M).Value = "no show" Then
               ws.Range("U" & M).EntireRow.Hidden = True
           End If
       Next M
   Next ws
End Sub
Debug takes me to the line: "If ws.Range("U" & M).Value = "no show" Then"

Any ideas why I get the error or better yet, how to fix the macro.

The value in the worksheets use this formula: =IF(ABS(SUMPRODUCT(E94:S94))=0,"no show","show")

There can be 300 lines in a worksheet or 250 lines.

Thank you
 
Last edited by a moderator:
Hi, as to hide rows a loop is useless just using Excel basics like a filter for example …​
​
 
I was not stating for the worksheets loop but for the rows loop​
like any Excel beginner can achieve manually via a filter (easier & faster) …​
​
 
To address your specific issue, most likely you have one or more error values in column U. You need to test for those first.
 
Thank you Marc L and Debaser.
Is there a simple macro to turn on the filter as opposed to checking the value in each row?
Thanks
 
Hi everyone- I need help as I am a real novice with Macros in Excel. I need 2 simple macros. I have a workbook with 85 worksheets. In column U (cell=U5) I have a filter that has 2 options: "show" and "no show". I need a macro to go through all worksheets and select only "show" and a second macro that will select both "show" and "no show". This is the first problem. The second step (after the first is resolved) is that each worksheet also has 2 settings using the Excel Outline feature. I would ultimately like to incorporate after selecting the "show" in the Filter to then select the Number 1 of the outline feature. In the other macro (selecting the "show" and the "no show' of the Filter to the select the Number 2 of the Outline Feature. Any help in this would be greatly appreciated. Thank you in advance for any help that you all can give.
 
Here is a sample file (only 3 worksheets). The actual file has about 80 worksheets
 

Attachments

  • 2020 Sample fileV1.xlsm
    258.4 KB · Views: 2
oldmaninla
One sample... 2nd version
Usage: Select any sheets cell F6 ( Revenue ) to change output.
This will change visible sheets output ... or can You see more sheets in one time?
 

Attachments

  • 2020 Sample fileV1.xlsb
    149.7 KB · Views: 3
Last edited:
@vletm - thank you. I am so much a novice, how would you reverse this macro should I want to return everything back to the way it was ("show" & "no show") & the Number 2 of the outline.

I really appreciate you taking the time to assist me! Thx
 
Hi-
thank you a fast reply
I didn’t run it yet but was reading the code
I’ll try later.
does this macro go through all the worksheets or only the active one? I have 85 worksheets and want it to do all of them at “one time”
Thank you
 
oldmaninla
About Your ... does ...
Did You read my writing about usage?
Usage: Select any sheets cell F6 ( Revenue ) to change output.
This will change visible sheets output ... or can You see more sheets in one time?
... as You've read it and that code ... it would change output any ( = selected sheet )
and mark other sheets ready for that.
You skipped that my question too ... I can see one sheet in one time,
that's why other sheets would change ... it those would select.
Please, a question normally needs an answer too.
 
oldmaninla
Okay - You can see ... usually ... one sheet at a time --- then
--- what matters about other sheets?
You can see other sheets after You've selected those.

One more time about Usage: Select any sheets cell F6 ( Revenue ) to change output.
Means that
#1 select any sheet - okay?
#2 select cell F6 ( There is Revenue -text ) - okay?
#3 You should see from Statusbar ( lower of Window ) something - okay?
#4 Sheet should filter or unfilter depends on previous selections state.
#5 That's all
... if You'll select after above any other sheet then it will filter as Your selection - that's all.

That Your cell F6 selection and selection of sheet runs those sample macros - that's all.
Where did I say something about Your but sentence-sentence?
Test it and watch screen - okay?
 
oldmaninla
Could You write with few words - How did You try to run the macro?
>> Did You test some with #12 reply's file or something else?
>> Did You follow my steps #18 reply?
... Is there any step which asks Your to ... run ... something?
>> Are marcos enabled all the time before You've opened that file?
 
Hi vletm,
Here is what I figured out (that works). Thank you for you guidance.
Code: 
Sub ShowOnly_1()
'
' ShowOnly_1 Macro
'

'
Dim ws As Worksheet

For Each ws In Worksheets

          If ws.Visible = True Then
          ws.Activate
              ActiveSheet.Range("$A$5:$A$618").AutoFilter Field:=1, Criteria1:="show"

End If

Next

End Sub
=======================================================================
Sub show_noshow_all_2()
'
' show_now_all_2 Macro
'

'
     Dim ws As Worksheet

For Each ws In Worksheets
    If ws.Visible = True Then
      ws.Activate
        ActiveSheet.Range("$A$5:$A$618").AutoFilter Field:=1, Criteria1:="=no show" _
        , Operator:=xlOr, Criteria2:="=show"

     End If
Next

End Sub
 
Last edited by a moderator:
I had to move the filter to column 1 as I couldn't figure out how to use the original filter in column "AA". Any ideas? I tried changing the AutoFilter Field to 27 but that didn't seem to work.
Thanks
 
oldmaninla
Okay ... I see ... zero.
Those Your #22 macros have done someone.

I asked few basic questions ...
... and You skipped all of those ... hmm?

If You could do ... try to do those steps
which I wrote to #18 reply
with my sent file
then I could give more comments.
 
You must log in or register to reply here.
Back
Top