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

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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

E
  • Solved
Macro to print a table filling from a sheet then move to the next row on the sheet
Replies
3
Views
177
Peter_SSs
Peter_SSs
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
238
krunal123
K
D
  • Question Question
VBA to Remove Text Characters From Strings
Replies
5
Views
892
Fuji
F
H
  • Question Question
An idea on handling serial numbers?
Replies
5
Views
360
Micron
M
T
  • Question Question
Controlled Data Entry - Bar Code reader to complete 6 columns on a row - then return to the next row
Replies
2
Views
250
texmansru47
T

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

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