VBA code to prevent row duplication in a macro

[Roxie#2013]

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!

[Richard Buttrey]

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)