+ Reply to Thread
Results 1 to 2 of 2

Getting error while no duplicate in the sheets

  1. #1
    Registered User
    Join Date
    03-05-2017
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    29

    Getting error while no duplicate in the sheets

    HI All,

    When I am using the below code for deleting the duplicate I am getting error 1004 only when sheets does not have duplicate.
    Please can some one help me to avoid this error.
    Sub duplicateDelNH31()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim lr, lr2
    Dim j As Long, i As Long
    Set s1 = Sheets("NH3"): Set s2 = Sheets("Utility")
    lr = s1.Cells(1).CurrentRegion.Resize(, 1).Value
    lr2 = s2.Cells(1).CurrentRegion.Resize(, 1).Value
    For j = 2 To UBound(lr2)
    For i = 2 To UBound(lr)
    If lr2(j, 1) = lr(i, 1) Then lr2(j, 1) = vbNullString: Exit For
    Next i
    Next j
    s2.Columns(1).ClearContents
    s2.Cells(1).Resize(UBound(lr2)) = lr2
    s2.Columns(1).SpecialCells(4).EntireRow.Delete
    MsgBox "Task Completed"

    End Sub

    Thanks in advance.

    Regards,Waqar

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: Getting error while no duplicate in the sheets

    Untested, but try:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Project - Sheets duplicate
    By JPGraphX in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-18-2014, 12:13 PM
  2. Help with duplicate sheets using VBA
    By qussai in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2012, 12:21 PM
  3. Finding duplicate across many sheets
    By hero96559 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-28-2012, 08:38 AM
  4. Macro that creates duplicate sheets. Random Error.
    By timmtamm in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-07-2011, 12:25 PM
  5. How to duplicate many Sheets?
    By clazerb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-02-2010, 09:11 AM
  6. msgbox on duplicate sheets
    By deletedalien in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2010, 04:55 PM
  7. Duplicate Sheets
    By Vetequk in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2009, 07:50 AM
  8. duplicate sheets
    By LaurieB in forum Excel General
    Replies: 4
    Last Post: 04-19-2006, 09:00 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1