This loads the data onto Sheets("GROSS")
<o></o>
<o></o>
<o></o>
Occasionally, I have to delete a range “A:Y”<o></o>
Help me find FullName on Sheet(“GROSS”) and delete that range. I can’t delete the entire row due to labels and formulae in Columns AA:AG<o></o>
<o></o>
I’m thinking another CommandButton and UserForm TextBox??? That I can handle. It’s the Find and delete code that’s got my wheels spinning.
Thanks for your consideration.<o></o>
Code:
[FONT=Arial][FONT=Verdana]Private Sub CommandButton1_Click()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Sheets("GROSS").Unprotect<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Sheets("NET").Unprotect<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Sheets("<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">PO</st1:place>").Unprotect<o:p></o:p>[/FONT][/FONT]
[FONT=Arial]<o:p></o:p>[/FONT]
[FONT=Arial][FONT=Verdana]Application.ScreenUpdating = True<o:p></o:p>[/FONT][/FONT]
[FONT=Arial]<o:p></o:p>[/FONT]
[FONT=Arial][FONT=Verdana]Dim iRow As Long<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Dim ws As Worksheet<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Dim FullName As String<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Dim CLoc As Range<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]Set ws = Worksheets("GROSS")<o:p></o:p>[/FONT][/FONT]
[FONT=Arial]<o:p></o:p>[/FONT]
[FONT=Arial][FONT=Verdana] FullName = Me.ComboBox1.Value<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] Set CLoc = ws.Columns("A:A").Find(What:=FullName, After:=ws.Cells(10, 1), LookIn:= _<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:= _<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] xlNext, MatchCase:=False, SearchFormat:=False)<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] If CLoc Is Nothing Then<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] iRow = ws.Cells(Rows.Count, 1) _<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] .End(xlUp).Offset(1, 0).Row<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] Else<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] iRow = CLoc.Row<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana] End If<o:p></o:p>[/FONT][/FONT]
[FONT=Arial]<o:p></o:p>[/FONT]
[FONT=Arial][FONT=Verdana]'copy the data to the GROSS Sheet<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]ws.Cells(iRow, 1).Value = Me.ComboBox1.Value<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]ws.Cells(iRow, 2).Value = Me.TextBox4.Value<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]ws.Cells(iRow, 3).Value = Me.TextBox5.Value<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]ws.Cells(iRow, 4).Value = Me.TextBox6.Value<o:p></o:p>[/FONT][/FONT]
[FONT=Arial][FONT=Verdana]etc…etc[/FONT][/FONT]
<o></o>
<o></o>
Occasionally, I have to delete a range “A:Y”<o></o>
Help me find FullName on Sheet(“GROSS”) and delete that range. I can’t delete the entire row due to labels and formulae in Columns AA:AG<o></o>
<o></o>
I’m thinking another CommandButton and UserForm TextBox??? That I can handle. It’s the Find and delete code that’s got my wheels spinning.
Thanks for your consideration.<o></o>