Delete data from Row

L

lovejanu4u

New Member
Joined
Apr 29, 2019
Messages
36
Suggest with deleting the selected data from sheet through userform, and paste in other sheet4 & Then make sequence of serial number(A column) in sheet1, from 1 to 1500.


Help me for above query in vba excel.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Suggest you provide more details. Sheet Names. Locations of data to be deleted/cut and locations of what to paste. Any criteria for this request. VBA is not generic but is code that is written for specific issues. Be specific.
 
Upvote 0
Code: 
[COLOR=#ff0000]'Double entry restricted.[/COLOR]

Private Sub CommandButton4_Click()
Dim a As Long
Dim b As Long
Dim x As Long
Dim i As Long
Dim sk1 As Range


Set sk1 = Sheets("PRC Training Database").Range("B3:B1048576")


x = Sheets("PRC Training Database").Range("B" & Rows.Count).End(xlUp).Row


For i = 3 To x


a = Sheets("PRC Training Database").Cells(i, 2).Value


If TextBox1.Text = "" Then
MsgBox "Enter Employee ID.No & Data."
TextBox1.SetFocus
Exit Sub
End If


If a = TextBox1.Text Then


MsgBox "This ID No already exists,Please update the employee Data"
TextBox1.SetFocus
Exit Sub
End If


Next i


Call adddata1


End Sub


[COLOR=#ff0000]'Add Data button[/COLOR]


Private Sub adddata1()
Dim x As Long
Dim y As Worksheet
Set y = Sheets("PRC Training Database")
x = y.Range("B" & Rows.Count).End(xlUp).Row
With y
.Cells(x + 1, "B").Value = TextBox1.Text
.Cells(x + 1, "C").Value = TextBox2.Text
.Cells(x + 1, "D").Value = TextBox3.Text
.Cells(x + 1, "E").Value = TextBox4.Text
.Cells(x + 1, "F").Value = TextBox5.Text
.Cells(x + 1, "G").Value = TextBox6.Text
.Cells(x + 1, "H").Value = TextBox7.Text
.Cells(x + 1, "I").Value = TextBox8.Text
.Cells(x + 1, "J").Value = TextBox9.Text
.Cells(x + 1, "K").Value = TextBox10.Text
.Cells(x + 1, "L").Value = TextBox11.Text
.Cells(x + 1, "M").Value = TextBox12.Text
.Cells(x + 1, "N").Value = TextBox13.Text
.Cells(x + 1, "O").Value = TextBox14.Text
.Cells(x + 1, "P").Value = TextBox15.Text
.Cells(x + 1, "Q").Value = TextBox16.Text
.Cells(x + 1, "R").Value = TextBox17.Text
.Cells(x + 1, "S").Value = TextBox18.Text
.Cells(x + 1, "T").Value = TextBox19.Text
.Cells(x + 1, "U").Value = TextBox20.Text
.Cells(x + 1, "V").Value = TextBox21.Text
.Cells(x + 1, "W").Value = TextBox22.Text
.Cells(x + 1, "X").Value = TextBox23.Text
.Cells(x + 1, "Y").Value = TextBox24.Text
.Cells(x + 1, "Z").Value = TextBox25.Text
.Cells(x + 1, "AA").Value = TextBox26.Text
.Cells(x + 1, "AB").Value = TextBox27.Text
.Cells(x + 1, "AC").Value = TextBox28.Text
.Cells(x + 1, "AD").Value = TextBox29.Text
.Cells(x + 1, "AE").Value = TextBox30.Text
.Cells(x + 1, "AF").Value = TextBox31.Text
.Cells(x + 1, "AG").Value = TextBox32.Text
.Cells(x + 1, "AH").Value = TextBox33.Text
.Cells(x + 1, "AI").Value = TextBox34.Text
.Cells(x + 1, "AJ").Value = TextBox35.Text
.Cells(x + 1, "AK").Value = TextBox36.Text
.Cells(x + 1, "AL").Value = TextBox37.Text
.Cells(x + 1, "AM").Value = TextBox38.Text
.Cells(x + 1, "AN").Value = TextBox39.Text
.Cells(x + 1, "AO").Value = TextBox40.Text
.Cells(x + 1, "AP").Value = TextBox41.Text
.Cells(x + 1, "AQ").Value = TextBox42.Text
.Cells(x + 1, "AR").Value = TextBox43.Text
.Cells(x + 1, "AS").Value = TextBox44.Text
.Cells(x + 1, "AT").Value = TextBox45.Text
.Cells(x + 1, "AU").Value = TextBox46.Text
.Cells(x + 1, "AV").Value = TextBox47.Text
.Cells(x + 1, "AW").Value = TextBox48.Text
.Cells(x + 1, "AX").Value = TextBox49.Text
.Cells(x + 1, "AY").Value = TextBox50.Text
.Cells(x + 1, "AZ").Value = TextBox51.Text
.Cells(x + 1, "BA").Value = TextBox52.Text
.Cells(x + 1, "BB").Value = TextBox53.Text
.Cells(x + 1, "BC").Value = TextBox54.Text
.Cells(x + 1, "BD").Value = TextBox55.Text
.Cells(x + 1, "BE").Value = TextBox56.Text
.Cells(x + 1, "BF").Value = TextBox57.Text
.Cells(x + 1, "BG").Value = TextBox58.Text
.Cells(x + 1, "BH").Value = TextBox59.Text
.Cells(x + 1, "BI").Value = TextBox60.Text
.Cells(x + 1, "BJ").Value = TextBox61.Text
.Cells(x + 1, "BK").Value = TextBox62.Text
.Cells(x + 1, "BL").Value = TextBox63.Text
.Cells(x + 1, "BM").Value = TextBox64.Text
.Cells(x + 1, "BN").Value = TextBox65.Text
.Cells(x + 1, "BO").Value = TextBox66.Text
.Cells(x + 1, "BP").Value = TextBox67.Text
.Cells(x + 1, "BQ").Value = TextBox68.Text
.Cells(x + 1, "BR").Value = TextBox69.Text








End With


'clear the data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
TextBox15.Text = ""
TextBox16.Text = ""
TextBox17.Text = ""
TextBox18.Text = ""
TextBox19.Text = ""
TextBox20.Text = ""
TextBox21.Text = ""
TextBox22.Text = ""
TextBox23.Text = ""
TextBox24.Text = ""
TextBox25.Text = ""
TextBox26.Text = ""
TextBox27.Text = ""
TextBox28.Text = ""
TextBox29.Text = ""
TextBox30.Text = ""
TextBox31.Text = ""
TextBox32.Text = ""
TextBox33.Text = ""
TextBox34.Text = ""
TextBox35.Text = ""
TextBox36.Text = ""
TextBox37.Text = ""
TextBox38.Text = ""
TextBox39.Text = ""
TextBox40.Text = ""
TextBox41.Text = ""
TextBox42.Text = ""
TextBox43.Text = ""
TextBox44.Text = ""
TextBox45.Text = ""
TextBox46.Text = ""
TextBox47.Text = ""
TextBox48.Text = ""
TextBox49.Text = ""
TextBox50.Text = ""
TextBox51.Text = ""
TextBox52.Text = ""
TextBox53.Text = ""
TextBox54.Text = ""
TextBox55.Text = ""
TextBox56.Text = ""
TextBox57.Text = ""
TextBox58.Text = ""
TextBox59.Text = ""
TextBox60.Text = ""
TextBox61.Text = ""
TextBox62.Text = ""
TextBox63.Text = ""
TextBox64.Text = ""
TextBox65.Text = ""
TextBox66.Text = ""
TextBox67.Text = ""
TextBox68.Text = ""
TextBox69.Text = ""




End Sub

[COLOR=#ff0000]
'Search Button[/COLOR]

Private Sub CommandButton1_Click()
Dim x As Long
Dim y As Long
x = Sheets("PRC Training Database").Range("b" & Rows.Count).End(xlUp).Row
For y = 2 To x


If Sheets("PRC Training Database").Cells(y, 2).Text = TextBox1.Value Then


TextBox1.Text = Sheets("PRC Training Database").Cells(y, 2)
TextBox2.Text = Sheets("PRC Training Database").Cells(y, 3)
TextBox3.Text = Sheets("PRC Training Database").Cells(y, 4)
TextBox4.Text = Sheets("PRC Training Database").Cells(y, 5)
TextBox5.Text = Sheets("PRC Training Database").Cells(y, 6)
TextBox6.Text = Sheets("PRC Training Database").Cells(y, 7)
TextBox7.Text = Sheets("PRC Training Database").Cells(y, 8)
TextBox8.Text = Sheets("PRC Training Database").Cells(y, 9)
TextBox9.Text = Sheets("PRC Training Database").Cells(y, 10)
TextBox10.Text = Sheets("PRC Training Database").Cells(y, 11)
TextBox11.Text = Sheets("PRC Training Database").Cells(y, 12)
TextBox12.Text = Sheets("PRC Training Database").Cells(y, 13)
TextBox13.Text = Sheets("PRC Training Database").Cells(y, 14)
TextBox14.Text = Sheets("PRC Training Database").Cells(y, 15)
TextBox15.Text = Sheets("PRC Training Database").Cells(y, 16)
TextBox16.Text = Sheets("PRC Training Database").Cells(y, 17)
TextBox17.Text = Sheets("PRC Training Database").Cells(y, 18)
TextBox18.Text = Sheets("PRC Training Database").Cells(y, 19)
TextBox19.Text = Sheets("PRC Training Database").Cells(y, 20)
TextBox20.Text = Sheets("PRC Training Database").Cells(y, 21)
TextBox21.Text = Sheets("PRC Training Database").Cells(y, 22)
TextBox22.Text = Sheets("PRC Training Database").Cells(y, 23)
TextBox23.Text = Sheets("PRC Training Database").Cells(y, 24)
TextBox24.Text = Sheets("PRC Training Database").Cells(y, 25)
TextBox25.Text = Sheets("PRC Training Database").Cells(y, 26)
TextBox26.Text = Sheets("PRC Training Database").Cells(y, 27)
TextBox27.Text = Sheets("PRC Training Database").Cells(y, 28)
TextBox28.Text = Sheets("PRC Training Database").Cells(y, 29)
TextBox29.Text = Sheets("PRC Training Database").Cells(y, 30)
TextBox30.Text = Sheets("PRC Training Database").Cells(y, 31)
TextBox31.Text = Sheets("PRC Training Database").Cells(y, 32)
TextBox32.Text = Sheets("PRC Training Database").Cells(y, 33)
TextBox33.Text = Sheets("PRC Training Database").Cells(y, 34)
TextBox34.Text = Sheets("PRC Training Database").Cells(y, 35)
TextBox35.Text = Sheets("PRC Training Database").Cells(y, 36)
TextBox36.Text = Sheets("PRC Training Database").Cells(y, 37)
TextBox37.Text = Sheets("PRC Training Database").Cells(y, 38)
TextBox38.Text = Sheets("PRC Training Database").Cells(y, 39)
TextBox39.Text = Sheets("PRC Training Database").Cells(y, 40)
TextBox40.Text = Sheets("PRC Training Database").Cells(y, 41)
TextBox41.Text = Sheets("PRC Training Database").Cells(y, 42)
TextBox42.Text = Sheets("PRC Training Database").Cells(y, 43)
TextBox43.Text = Sheets("PRC Training Database").Cells(y, 44)
TextBox44.Text = Sheets("PRC Training Database").Cells(y, 45)
TextBox45.Text = Sheets("PRC Training Database").Cells(y, 46)
TextBox46.Text = Sheets("PRC Training Database").Cells(y, 47)
TextBox47.Text = Sheets("PRC Training Database").Cells(y, 48)
TextBox48.Text = Sheets("PRC Training Database").Cells(y, 49)
TextBox49.Text = Sheets("PRC Training Database").Cells(y, 50)
TextBox50.Text = Sheets("PRC Training Database").Cells(y, 51)
TextBox51.Text = Sheets("PRC Training Database").Cells(y, 52)
TextBox52.Text = Sheets("PRC Training Database").Cells(y, 53)
TextBox53.Text = Sheets("PRC Training Database").Cells(y, 54)
TextBox54.Text = Sheets("PRC Training Database").Cells(y, 55)
TextBox55.Text = Sheets("PRC Training Database").Cells(y, 56)
TextBox56.Text = Sheets("PRC Training Database").Cells(y, 57)
TextBox57.Text = Sheets("PRC Training Database").Cells(y, 58)
TextBox58.Text = Sheets("PRC Training Database").Cells(y, 59)
TextBox59.Text = Sheets("PRC Training Database").Cells(y, 60)
TextBox60.Text = Sheets("PRC Training Database").Cells(y, 61)
TextBox61.Text = Sheets("PRC Training Database").Cells(y, 62)
TextBox62.Text = Sheets("PRC Training Database").Cells(y, 63)
TextBox63.Text = Sheets("PRC Training Database").Cells(y, 64)
TextBox64.Text = Sheets("PRC Training Database").Cells(y, 65)
TextBox65.Text = Sheets("PRC Training Database").Cells(y, 66)
TextBox66.Text = Sheets("PRC Training Database").Cells(y, 67)
TextBox67.Text = Sheets("PRC Training Database").Cells(y, 68)
TextBox68.Text = Sheets("PRC Training Database").Cells(y, 69)
TextBox69.Text = Sheets("PRC Training Database").Cells(y, 70)
End If
Next y
End Sub


In (sheet1)PRC Training Database sheet i have Rows(1503) & Columns(A to BR).
A is for Serial Number.
I want delete button for the userform, where my data has to be search, deleted and it has to be saved in other sheet3.








alansidman said:
Suggest you provide more details. Sheet Names. Locations of data to be deleted/cut and locations of what to paste. Any criteria for this request. VBA is not generic but is code that is written for specific issues. Be specific.
Click to expand...
 
Last edited by a moderator:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
An idea on handling serial numbers?
Replies
5
Views
98
Micron
M
A
  • Solved
Automatic Serial Number before Text in a Cell
2 3
Replies
25
Views
411
Joe4
Joe4
S
  • Question
Program Button to Find, Copy, Paste to New Sheet based on Value in column
Replies
0
Views
174
samss919
S
B
  • Solved
VBA Help - Same Function needed for multiple sheets
Replies
2
Views
264
birdman15
B
H
  • Question
data extract between sheets For the value entered
Replies
1
Views
162
LazyBug
LazyBug

Forum statistics

Threads
1,215,480
Messages
6,125,047
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top