none
change macro to work on rows RRS feed

  • Question

  • hello I was given this macro BY Ashidacchi and it did a great job 

    but I tried to change it to work on rows in range d7:d118 I was unsuccessful can someone help me out 

    I did change all words with column to row

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not (Intersect(Target, Range("E5:BL5")) Is Nothing) Then
            If (Target = "0") Then
                Columns(Target.Column).Hidden = True
            End If
        End If
    End Sub
    

    I think this is the error 

    Worksheet_Change

    but have no clue what to change it too or any clue what I am doing 


    Sunday, January 26, 2020 4:14 AM

Answers

  • Hi,

    I'd like to recommend 
      1) to provide the error message (or error Number)
      2) to distinguish between Syntax error and Run-time error
      3) to copy the actual code lines in the VBA Editor & paste it to your post
      4) to purchase a book about Excel VBA for beginners.

       # comment about 2)
          If you type "rows" or "row" in the VBA Editor, their first character should be changed into Capital automatically.  So, I suppose your code would be written in your post, instead of Copy&Paste.

    The issue is not reproduced.
        
    Private Sub Worksheet_Change(ByVal Target As Range)
        ' --- hide Row
        If Not (Intersect(Target, Range("d7:d118")) Is Nothing) Then
            If (Target = "0") Then
                Rows(Target.Row).Hidden = True
            End If
        End If
    End Sub

    I suspect [Design Mode] (in [Developer]) is ON.
    When it is ON, any macro won't work.
        

    Regards, 

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Sunday, January 26, 2020 11:30 PM insert picture
    • Marked as answer by Yahya123 Monday, January 27, 2020 1:17 AM
    Sunday, January 26, 2020 11:26 PM

All replies

  • Hi,

    According to my memory, your requirement is like this:
    When a value of a cell is changed to "0" in a certain range of a worksheet, the column of the cell should be hidden.

    If this is correct, I'd like to confirm:1) Did you place the macro in the worksheet where you want to make columns hidden?
    2) Why did you change a value of the range?  i.e. Range("E5:BL5") to Range("D7:D118")  
    Regards,

    Ashidacchi -- http://hokusosha.com

    Sunday, January 26, 2020 9:34 AM
  • 1)yes and works great 

    I have  used it on a different sheet for a different purpose and still words great.  

    2)   Range("D7:D118") reason I changed It  was because I want to hide the rows now in that range 

    I had a crack to make the macro work to hide rows with value of 0 it didn't work 

    Sunday, January 26, 2020 9:57 AM
  • Hi,

    2)   Range("D7:D118") reason I changed It  was because I want to hide the rows now in that range 

    Sorry, I misunderstood your first post.
    A code to hide rows is very similar to a code to hide columns.
    Please show your code which doesn't work as expected.

    Regards,

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Sunday, January 26, 2020 12:48 PM
    Sunday, January 26, 2020 12:45 PM
  • Private Sub Worksheet_Change(ByVal Target As Range)
        If Not (Intersect(Target, Range("d7:d118")) Is Nothing) Then
            If (Target = "0") Then
                rows(Target.row).Hidden = True
            End If
        End If
    End Sub

    this is the code I did 

    this is the error

    Compile error:

    Ambiguous name detected: Worksheet_change

    • Edited by Yahya123 Sunday, January 26, 2020 10:05 PM
    Sunday, January 26, 2020 8:49 PM
  • Hi,

    I'd like to recommend 
      1) to provide the error message (or error Number)
      2) to distinguish between Syntax error and Run-time error
      3) to copy the actual code lines in the VBA Editor & paste it to your post
      4) to purchase a book about Excel VBA for beginners.

       # comment about 2)
          If you type "rows" or "row" in the VBA Editor, their first character should be changed into Capital automatically.  So, I suppose your code would be written in your post, instead of Copy&Paste.

    The issue is not reproduced.
        
    Private Sub Worksheet_Change(ByVal Target As Range)
        ' --- hide Row
        If Not (Intersect(Target, Range("d7:d118")) Is Nothing) Then
            If (Target = "0") Then
                Rows(Target.Row).Hidden = True
            End If
        End If
    End Sub

    I suspect [Design Mode] (in [Developer]) is ON.
    When it is ON, any macro won't work.
        

    Regards, 

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Sunday, January 26, 2020 11:30 PM insert picture
    • Marked as answer by Yahya123 Monday, January 27, 2020 1:17 AM
    Sunday, January 26, 2020 11:26 PM
  • Private Sub Worksheet_Change(ByVal Target As Range)
        If Not (Intersect(Target, Range("E5:Bm5")) Is Nothing) Then
            If (Target = "0") Then
                Columns(Target.Column).Hidden = True
            End If
        End If
        ' --- hide Row
        If Not (Intersect(Target, Range("d7:d118")) Is Nothing) Then
            If (Target = "0") Then
                Rows(Target.Row).Hidden = True
            End If
        End If
    End Sub

    this worked 


    I just removed 

    Private Sub Worksheet_Change(ByVal Target As Range)

    • Edited by Yahya123 Monday, January 27, 2020 1:14 AM
    Monday, January 27, 2020 1:13 AM