View original (French)

Select method of the Range class

Solved
Nashimok Posted messages 19 Status Member -  
Nashimok Posted messages 19 Status Member -
Hello everyone,

I have a VBA code that allows me to create a chart based on the number of occurrences of each entry.

Some entries have a zero occurrence, and I would like them not to appear in my chart.
To do this, before creating the chart, I added the following lines of code that allow me to hide the rows for which the occurrence is zero. 

 ''''''''''''''''''''''Hiding zero occurrences'''''''''''''''''''''''''''' line2 = 27 Do Until IsEmpty(Cells(line2, "B")) If Cells(line2, "B").Value = 0 Then Rows(line2).Select Selection.EntireRow.Hidden = True line2 = line2 + 1 Else line2 = line2 + 1 End If Loop


This code works perfectly when it's in a separate module, but when I add it to my main code that is activated by clicking a button, it no longer works...

I get a runtime error 1004 that says "the Select method of the Range class failed" ... The debugger indicates that this line is causing an issue: 

Rows(line2).Select


I tried replacing 
Rows(line2).Select

with 
Range(line2:line2).Select


but that doesn't work either...

Any idea why it works in a module and not in the global code?
Thanks in advance!

Configuration: Windows 7 / Chrome 53.0.2785.116

7 answers

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,

    without seeing the code or the general part of the code since it's what prevents...

    but already you could improve your code
    in VBA, we avoid the "select" as much as possible and whatever the if, you increment.. 

    If Cells(ligne2, "B") = 0 Then
     Rows(ligne2).Hidden = True
     End If
     ligne2 = ligne2 + 1


    --
     Michel
    0
    1. Nashimok Posted messages 19 Status Member
       
      Thank you for the advice! It’s true that I often use .select, rarely managing to substitute it....

      While browsing the forums, I found a solution that works, which is to call the module where my code runs: 

      Private Sub CommandButton3_Click() Call Résumé_graphique End Sub


      However, the problem is not resolved if I leave my global code... Besides, I tried again, and the error moved to another .Select that worked perfectly fine before.... and simply allows me to deselect the chart I just created... 
      Cells(1, "A").Select


      There must be a recurring error in my code... Is there a way to send all the code in a file, or do I need to copy it entirely into a message here?
      0
    2. Nashimok Posted messages 19 Status Member
       
      Hello, here is the complete code at this link:

      http://www.cjoint.com/c/FIzh6ICnFHf

      It works in a module, but not when I insert it under the Sub CommandButton_Click() of my sheet.

      Thanks again for your help!
      0