Rows Hide and Unhide

[sgmpatnaik]

Hello Sir,

I have created a code to update the data from One Sheet to Another Sheet that code works perfect but there is one problem i am facing that i tried to hide the certain range of rows in that sheet so i searched in Google and i got a example code. With That Example Code i did some modification as per my requirements but now there is one problem that is i want to change the range of the code but i can't when ever i run the code after change the range then i am getting the error msg as Type Miss Match. for your knowledge i am mention the code below please have look and kindly give the step by step instruction.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call UnprotectSheets
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
      
    End With
    If Not Application.Intersect(Range("E9:E52"), Target) Is Nothing Then
        Rows(Target.Row + 1).Hidden = Target.Value <= 0
    End If
    Sheets("Purchase").PivotTables("PivotTable2").PivotCache.Refresh
    Range("b:b").ColumnWidth = 27
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    Call ProtectSheets
End Sub

When Ever i want to change the Range then i am getting the error, but when i put the same range then i am not getting any error msg and code works fine

Your Replay is Highly Appreciate

Thanking You

 

[RanOtherwise]

Hi sgmpatnaik!

It looks for the most part like your code should be working OK.

So, can you tell us which is the Range that you are trying to change (and how and why), as well as what is the line where you are getting the Type Mismatch problem?

Thanks,
-Brad

 

[sgmpatnaik]

Hi Ran

Thanks for Your Replay

actually now i want to change the Range is ("D9:D52")

When i press the Update Button then i am getting the Error Msg is
Run Time Error '13'
Type Mismatch

when i press the Debug Button then the Error shown in the given code not in the Update Button Code and also it's showing another one is

Can't execute code in break mode

after press the Ok button then the below line is highlighted

Rows(Target.Row + 1).Hidden = Target.Value <= 0

Here is My Another Code which is update the data from one sheet to another Sheet

Sub Up_Date1()

  Dim rng As Range
  Dim Temp As Variant
  Dim i As Long
  Dim a As Long
  Dim rng_dest As Range
  
  
  Application.ScreenUpdating = False
  
  i = 6
  
  Set rng_dest = Sheets("Day Book").Range("L:V")
  
  
  ' Find first empty row in columns H:L on sheet Sales Register
  
  Do Until WorksheetFunction.CountA(rng_dest.Rows(i)) = 0
  
    i = i + 1
    
  Loop
  
  'Copy range B9:G50 on sheet Sales to Variant array
  
  Set rng = Sheets("Purchase").Range("B9:L52")
  
  ' Copy rows containing values to sheet Sales Register
  
  For a = 1 To rng.Rows.Count
    
    If WorksheetFunction.CountA(rng.Rows(a)) <> 0 Then
    
      rng_dest.Rows(i).Value = rng.Rows(a).Value
      
      'Copy Invoice number
  
      Sheets("Day Book").Range("D" & i).Value = Sheets("Purchase").Range("D2").Value
        
      'Copy Date
      
      Sheets("Day Book").Range("B" & i).Value = Sheets("Purchase").Range("L2").Value
          
      'Copy Voucher Type
      
      Sheets("Day Book").Range("G" & i).Value = Sheets("Purchase").Range("N1").Value
      'Copy Bill Date
      
      Sheets("Day Book").Range("E" & i).Value = Sheets("Purchase").Range("H2").Value
      'Copy Address
      
      Sheets("Day Book").Range("J" & i).Value = Sheets("Purchase").Range("H5").Value
      
      'Copy Company name
      
      Sheets("Day Book").Range("I" & i).Value = Sheets("Purchase").Range("H4").Value
      
      'Copy Type
      Sheets("Day Book").Range("H" & i).Value = Sheets("Purchase").Range("H3").Value
      
      'Copy Tin No.
      Sheets("Day Book").Range("K" & i).Value = Sheets("Purchase").Range("H6").Value
      
      'Copy Freight
      
      'Sheets("Day Book").Range("T" & i).Value = Sheets("Purchase").Range("D58").Value
      'Sheets("Day Book").Cells(NextRow, 20) = Sheets("Purchase").Range("I58").Value
      
      'Copy Loading
      
      'Sheets("Day Book").Range("U" & i).Value = Sheets("Purchase").Range("D59").Value
      
      'Copy Less Discount
      
      'Sheets("Day Book").Range("V" & i).Value = Sheets("Purchase").Range("D60").Value
      
      On Error GoTo NoBlanks
      Sheets("Day Book").Range("L6:L20000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
      
NoBlanks:
      Resume Next
      i = i + 1
        
    End If
        
  Next a
  
        
  Application.ScreenUpdating = True
  
    Sheets("Purchase").Range("B9:B52").Value = ""
    Sheets("Purchase").Range("D9:D52").Value = ""
    Sheets("Purchase").Range("L9:L52").Value = ""
    Sheets("Purchase").Range("D2").Value = ""
    Sheets("Purchase").Range("H2").Value = ""
    Sheets("Purchase").Range("L2").Value = ""
    'Sheets("Purchase").Range("E9:E52").Clear
    'Sheets("Purchase").Range("I58").Value = ""
    'Sheets("Purchase").Range("I59").Value = ""
    'Sheets("Purchase").Range("I60").Value = ""
    'Sheets("Sales").Range("E9:E52").Value = ""
    Sheets("Day Book").Range("C6:C20000").Formula = "=IF(B6="""","""",TEXT(B6,""dd/mm/yyyy""))"
    Sheets("Day Book").Range("F6:F20000").Formula = "=IF(E6="""","""",TEXT(E6,""dd/mm/yyyy""))"
    Sheets("Day Book").Range("Z6:Z20000").Formula = "=IF(G6=""Purchase"",S6+T6+U6+V6+W6+X6-Y6,0)"
    Sheets("Day Book").Range("AA6:AA20000").Formula = "=IF(G6=""Purchase"",0,S6+T6+U6+V6+W6+X6+-Y6)"
    Sheets("Day Book").Range("AH6:AH20000").Formula = "=IF(G6&H6="""","""",IF(OR(AND(G6=""Retail Invoice"",G6=""Tax Invoice""),AND(H6=""Cash Sale"")),"""",""Credit""))"
    Sheets("Day Book").Range("AL6:AL20000").Formula = "=X6+AA6"
    Sheets("Day Book").Range("AM6:AM20000").Formula = "=AB6+W6"
    Call ADDFRLO
    'Sheets("Day Book").Range("T7:T15000").Formula = "=SUM(P7+Q7+R7-S7)"
    'Sheets("Day Book").Range("AB7:AB15000").Formula = "=IF(OR(P7>0,W7>0),""True"","")"
    
End Sub

Thanking You

 

[Fluff]

What is the value of Target when you get the error?

 

[sgmpatnaik]

Hi Good Morning

Thanks for Your Replay

Nothing value i got on the error

Thanking You

 

[Fluff]

Try adding the line in red as shown

Private Sub Worksheet_Change(ByVal Target As Range)
  [COLOR=#ff0000]  If Target.CountLarge > 1 Then Exit Sub[/COLOR]
    Call UnprotectSheets
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With