+ Reply to Thread
Results 1 to 4 of 4

Type 13 error when deleting content

Hybrid View

arundh Type 13 error when deleting... 08-05-2013, 12:10 AM
Leith Ross Re: Type 13 error when... 08-05-2013, 12:24 AM
arundh Re: Type 13 error when... 08-05-2013, 12:41 AM
Leith Ross Re: Type 13 error when... 08-05-2013, 01:42 AM
  1. 08-05-2013, 12:10 AM #1
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Type 13 error when deleting content

    Hi I get a runtime error 13 when highlighting and deleting multiple cells at once within the rage define by the following code: 

    Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Not Intersect(Target, Range("B28:B46")) Is Nothing Then
        If Target.Value = "" Then
            Target.Offset(1).EntireRow.Hidden = True
        Else
            Target.Offset(1).EntireRow.Hidden = False
        End If
Application.ScreenUpdating = True
End If
End Sub
    The code is designed to hide/unhide the row below if something has been entered in the cell above. Rows 29 through 47 are hidden to the user when they first open the workbook. With the error, the rows do not hide automatically once the content has been deleted.

    The code below performs the same thing as the above, however, I do not get the error with the code below and the rows hide automatically when the content has been deleted. Any ideas as to how to avoid the error with the condensed code above? Thanks!

    Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
    If Range("B28").Value = 0 Then Range("B29").EntireRow.Hidden = True
    If Range("B29").Value = 0 Then Range("B30").EntireRow.Hidden = True
    If Range("B30").Value = 0 Then Range("B31").EntireRow.Hidden = True
    If Range("B31").Value = 0 Then Range("B32").EntireRow.Hidden = True
    If Range("B32").Value = 0 Then Range("B33").EntireRow.Hidden = True
    If Range("B33").Value = 0 Then Range("B34").EntireRow.Hidden = True
    If Range("B34").Value = 0 Then Range("B35").EntireRow.Hidden = True
    If Range("B35").Value = 0 Then Range("B36").EntireRow.Hidden = True
    If Range("B36").Value = 0 Then Range("B37").EntireRow.Hidden = True
    If Range("B37").Value = 0 Then Range("B38").EntireRow.Hidden = True
    If Range("B38").Value = 0 Then Range("B39").EntireRow.Hidden = True
    If Range("B39").Value = 0 Then Range("B40").EntireRow.Hidden = True
    If Range("B40").Value = 0 Then Range("B41").EntireRow.Hidden = True
    If Range("B41").Value = 0 Then Range("B42").EntireRow.Hidden = True
    If Range("B42").Value = 0 Then Range("B43").EntireRow.Hidden = True
    If Range("B43").Value = 0 Then Range("B44").EntireRow.Hidden = True
    If Range("B44").Value = 0 Then Range("B45").EntireRow.Hidden = True
    If Range("B45").Value = 0 Then Range("B46").EntireRow.Hidden = True
    If Range("B46").Value = 0 Then Range("B47").EntireRow.Hidden = True
        
    If Not Range("B28").Value = 0 Then Range("B29").EntireRow.Hidden = False
    If Not Range("B28").Value = 0 Then Range("B29").EntireRow.Hidden = False
    If Not Range("B29").Value = 0 Then Range("B30").EntireRow.Hidden = False
    If Not Range("B30").Value = 0 Then Range("B31").EntireRow.Hidden = False
    If Not Range("B31").Value = 0 Then Range("B32").EntireRow.Hidden = False
    If Not Range("B32").Value = 0 Then Range("B33").EntireRow.Hidden = False
    If Not Range("B33").Value = 0 Then Range("B34").EntireRow.Hidden = False
    If Not Range("B34").Value = 0 Then Range("B35").EntireRow.Hidden = False
    If Not Range("B35").Value = 0 Then Range("B36").EntireRow.Hidden = False
    If Not Range("B36").Value = 0 Then Range("B37").EntireRow.Hidden = False
    If Not Range("B37").Value = 0 Then Range("B38").EntireRow.Hidden = False
    If Not Range("B38").Value = 0 Then Range("B39").EntireRow.Hidden = False
    If Not Range("B39").Value = 0 Then Range("B40").EntireRow.Hidden = False
    If Not Range("B40").Value = 0 Then Range("B41").EntireRow.Hidden = False
    If Not Range("B41").Value = 0 Then Range("B42").EntireRow.Hidden = False
    If Not Range("B42").Value = 0 Then Range("B43").EntireRow.Hidden = False
    If Not Range("B43").Value = 0 Then Range("B44").EntireRow.Hidden = False
    If Not Range("B44").Value = 0 Then Range("B45").EntireRow.Hidden = False
    If Not Range("B45").Value = 0 Then Range("B46").EntireRow.Hidden = False
    If Not Range("B46").Value = 0 Then Range("B47").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
    Reply With QuoteRegister To Reply
  2. 08-05-2013, 12:24 AM #2
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Type 13 error when deleting content

    Hello arundh,

    This should fix the problem.
    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range
    
        Application.ScreenUpdating = False
        
            If Not Intersect(Target, Range("B28:B46")) Is Nothing Then
                For Each cell In Target
                    If cell.Value = "" Then
                        cell.Offset(1).EntireRow.Hidden = True
                    Else
                        cell.Offset(1).EntireRow.Hidden = False
                    End If
                Next cell
            End If
            
        Application.ScreenUpdating = True
        
End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 08-05-2013, 12:41 AM #3
    arundh is offline
    Registered User
    Join Date
    07-28-2013
    Location
    Chicago, Illinois
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Type 13 error when deleting content

    Works like a champ! Thank you for your quick reply!
    Reply With QuoteRegister To Reply
  4. 08-05-2013, 01:42 AM #4
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Type 13 error when deleting content

    Hello arundh,

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Error 13 - Type Mismatch for my macro code for deleting rows based on condition.
    By Whitenoise1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-19-2012, 07:56 PM
  2. type mismatch error when deleting cells
    By thigham2000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2008, 05:04 PM
  3. Type Mismatch Error when deleting a cell
    By Josiah in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-11-2008, 01:07 PM
  4. Textbox Content Type
    By KingG in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-12-2006, 10:30 AM
  5. Help: Compile error: type mismatch: array or user defined type expected
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-31-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1