+ Reply to Thread
Results 1 to 2 of 2

VBA code to prevent row duplication in a macro

  1. #1
    Registered User
    Join Date
    12-16-2013
    Location
    SF
    MS-Off Ver
    Excel 2003
    Posts
    15

    VBA code to prevent row duplication in a macro

    Hi,

    I have a macro i created with the following code (by the way..I am new to VBA so I originally created 4 separate macros and copied and pasted all the code into one parent macro. I did this thinking I'd use a command button with just code instead of macros) I've bolded the code at issue.

    Sub ConvertRemedy()
    Dim MyRange As Range
    Dim MyCell As Range

    Select Case MsgBox("Can't Undo this action." & " " & "Save Workbook First?", vbYesNoCancel)
    Case Is = vbYes
    ThisWorkbook.Save

    Case Is = vbCancel
    Exit Sub
    End Select

    Set MyRange = Selection
    For Each MyCell In MyRange

    If Not IsEmpty(MyCell) Then
    MyCell = Trim(MyCell)
    End If
    Next MyCell

    Set ws = Worksheets("Sheet1")
    ws.Rows(1).Insert
    Worksheets("Sheet1").Range("A1:e1").Value = _
    Array("Remedy Group Name", "Support Organization", "Org Manager Name", "Org Unit Name", "Org Tree")
    Range("a1:e1").Font.Bold = True
    Set sht = ActiveSheet



    Dim lrow As Long, lcol As Long, i As Long
    Dim myName As String, Start As String
    Const Rowno = 1
    Const Colno = 1
    Const Offset = 1
    On Error Resume Next
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet
    lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
    lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
    Start = Cells(Rowno, Colno).Address
    wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
    wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
    wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
    For i = Colno To lcol
    myName = Replace(Cells(Rowno, i).Value, " ", "_")
    If myName <> "" Then
    wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
    End If
    Next

    Range("MyData").Sort Key1:=Range("MyData").Cells(1, 1), _
    Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

    On Error Resume Next
    Columns("A").SpecialCells(xlBlanks).EntireRow.Delete

    End Sub



    Everything works fine except for one issue. If I run the macro more than once it inserts a new header row each time. The weird thing is it does this starting in row 2169 and each subsequent row after that. What code can I add to say 'if a header row has already been inserted, do not insert a duplicate'

    Thanks!

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA code to prevent row duplication in a macro

    Unfortunately your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


    That said are you sure you actually need a macro. We often find with this sort of stuff that there are perfectly straightforward Exel formulae that will do the job. Macros should only be a last resort.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. PREVENT DUPLICATION ENTRY in 2 DIFFERENT WORKFILES
    By Cathy81 in forum Excel General
    Replies: 2
    Last Post: 06-17-2014, 11:08 AM
  2. Times for Appointment in drop down list - prevent duplication?
    By cyuu in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2013, 07:12 PM
  3. how to prevent data duplication in excel 97-03
    By mostwanted in forum Excel General
    Replies: 1
    Last Post: 05-22-2009, 04:05 PM
  4. How do I prevent duplication of numbers in a column
    By waldo in forum Excel General
    Replies: 2
    Last Post: 03-05-2006, 02:40 PM
  5. [SOLVED] prevent duplication
    By Ben Blair in forum Excel General
    Replies: 1
    Last Post: 05-20-2005, 11:06 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