Removal of line of Data

G

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
771
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon,

I have created a userform that reads of one sheet and then updates another with the added info making the original info redundant.
I have a unique number for each enquiry raised but am having an issue adding a piece of code i have been given by a colleague at work.

I am trying to add the code to the submit button so that when it is saving it to the new sheet it also removes the old line.

It is taking info from "sheet1" and then adds it to a mangers name.
I have the following code that succesfully saves to the correct sheets as below, but need to add the following to remove the original line.

Rich (BB code): 
Private Sub CommandButton3_Click()
'Checks if info in fields  are missing
If Me.ComboBox4.Value = "" Then
    MsgBox "You need to complete the customer name"
        Else
            If Me.TextBox17.Value = "" Then
            MsgBox "You need to complete your question catogery"
                Else
                    If Me.JEN.Value = "" Then
                    MsgBox "Can you confirm you have checked the How Guide"
                        Else
                            If Me.CBoxAdd.Value = "" Then
                            MsgBox "You need to complete your question"
                                Else
                                    Application.Calculation = xlCalculationManual
 
                                    Application.ScreenUpdating = False
                                    'Opens file to
                                    'Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home
                                    Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
 
                                    Dim myBook As Workbook
 
                                    On Error Resume Next
                                    Set myBook = Application.Workbooks("FYVData.xls")
                                    On Error GoTo 0
 
                                    If myBook.ReadOnly Then
                                    MsgBox "This file is being used by someone else please try again in a minute"
                                    ActiveWorkbook.Close False
                                    Else
                                    'checks cmbcsm.value and save's to the sheet with that name in the workbook above
                                    Dim iRow As Long
                                    Dim ws As Worksheet
                                    Set ws = Worksheets(CmbCSM.Value)
                                        ws.AutoFilterMode = False
                                        ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 19).Value = Array(txtdate.Value, _
                                        TextBox18.Value, CmbCSM.Value, TextBox1.Value, TextBox2.Value, TextBox6.Value, TextBox5.Value, _
                                        TextBox3.Value, TextBox4.Value, TextBox7.Value, TextBox8.Value, TextBox14.Value, TextBox15.Value, _
                                        TextBox9.Value, JEN.Value, TextBox12.Value, ComboBox4.Value, TextBox17.Value, CBoxAdd.Value)
 
                                    'clear the data
                                        Me.txtdate.Value = ""
                                        Me.CmbCSM.Value = ""
                                        Me.TextBox1.Value = ""
                                        Me.TextBox2.Value = ""
                                        Me.TextBox6.Value = ""
                                        Me.TextBox3.Value = ""
                                        Me.TextBox5.Value = ""
                                        Me.TextBox4.Value = ""
                                        Me.TextBox7.Value = ""
                                        Me.TextBox8.Value = ""
                                        Me.TextBox14.Value = ""
                                        Me.TextBox9.Value = ""
                                        Me.JEN.Value = ""
                                        Me.TextBox12.Value = ""
                                        Me.ComboBox4.Value = ""
                                        Me.TextBox17.Value = ""
                                        Me.CBoxAdd.Value = ""
 
                                    Windows("FYVData.xls").Activate
                                    ActiveWindow.Close (True)
 
                                     Application.Calculation = xlCalculationAutomatic
 
                                            Application.ScreenUpdating = True
 
                                            MsgBox "Your Car Park has been submitted"
 
                                    Unload Me
                            End If
                    End If
            End If
    End If
End If
 
End Sub

I have been supplied the following code to remove the line. The unique number is in "TextBox18" and in column "D" in "Sheet1"
Rich (BB code): 
Sheets("Audit").Activate<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
With ActiveSheet<o:p></o:p>
       <o:p></o:p>
   LastRow = .Cells(.Rows.Count, "AA").End(xlUp).Row<o:p></o:p>
   For i = LastRow To 2 Step -1<o:p></o:p>
               <o:p></o:p>
   If .Cells(i, "AA").Value = 0 Then<o:p></o:p>
   .Rows(i).Delete<o:p></o:p>
   End If<o:p></o:p>
   Next i<o:p></o:p>
       <o:p></o:p>
   End With<o:p></o:p>
   <o:p></o:p>
   With Application<o:p></o:p>
   .Calculation = xlCalculationAutomatic<o:p></o:p>
<o:p>
</o:p>
<o:p></o:p>
<o:p>Hopefully someone can help me as i am wishing i had never taken the project on.</o:p>
<o:p></o:p>
<o:p>Many Thanks</o:p>
<o:p>Gavin</o:p>
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Gavin

Instead of "AA" shouldn't it be "D" which is the column you say the value you are looking for is.

Give that a try - there's a few of other things you should probably change, some of which I've mentioned earlier I think.

I can't sort any code right now but if you want I can take a closer look later and post back then.
 
Upvote 0
Good Afternoon,

I would appreciate it if you can have a look for me please.
I have adjusted the VB as follows as the info is relevant in "E" and not "D".

The code does actually run but debugs on line

Rich (BB code): 
ActiveWorkbook.Sheets("Sheet1").Activate

amendments are as follows at the moment
Rich (BB code): 
Private Sub CommandButton3_Click()
'Checks if info in fields  are missing
If Me.ComboBox4.Value = "" Then
    MsgBox "You need to complete the customer name"
        Else
            If Me.TextBox17.Value = "" Then
            MsgBox "You need to complete your question catogery"
                Else
                    If Me.JEN.Value = "" Then
                    MsgBox "Can you confirm you have checked the How Guide"
                        Else
                            If Me.CBoxAdd.Value = "" Then
                            MsgBox "You need to complete your question"
                                Else
                                    Application.Calculation = xlCalculationManual
                                    
                                    Application.ScreenUpdating = False
                                    'Opens file to
                                    'Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls" 'Home
                                    Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls"
                                    
                                    Dim myBook As Workbook
                                     
                                    On Error Resume Next
                                    Set myBook = Application.Workbooks("FYVData.xls")
                                    On Error GoTo 0
 
                                    If myBook.ReadOnly Then
                                    MsgBox "This file is being used by someone else please try again in a minute"
                                    ActiveWorkbook.Close False
                                    Else
                                    'checks cmbcsm.value and save's to the sheet with that name in the workbook above
                                    Dim iRow As Long
                                    Dim ws As Worksheet
                                    Set ws = Worksheets(CmbCSM.Value)
                                        ws.AutoFilterMode = False
                                        ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 19).Value = Array(txtdate.Value, _
                                        TextBox18.Value, CmbCSM.Value, TextBox1.Value, TextBox2.Value, TextBox6.Value, TextBox5.Value, _
                                        TextBox3.Value, TextBox4.Value, TextBox7.Value, TextBox8.Value, TextBox14.Value, TextBox15.Value, _
                                        TextBox9.Value, JEN.Value, TextBox12.Value, ComboBox4.Value, TextBox17.Value, CBoxAdd.Value)
                              
                                    'clear the data
                                        Me.txtdate.Value = ""
                                        Me.CmbCSM.Value = ""
                                        Me.TextBox1.Value = ""
                                        Me.TextBox2.Value = ""
                                        Me.TextBox6.Value = ""
                                        Me.TextBox3.Value = ""
                                        Me.TextBox5.Value = ""
                                        Me.TextBox4.Value = ""
                                        Me.TextBox7.Value = ""
                                        Me.TextBox8.Value = ""
                                        Me.TextBox14.Value = ""
                                        Me.TextBox9.Value = ""
                                        Me.JEN.Value = ""
                                        Me.TextBox12.Value = ""
                                        Me.ComboBox4.Value = ""
                                        Me.TextBox17.Value = ""
                                        Me.CBoxAdd.Value = ""
                                    
                                    Windows("FYVData.xls").Activate
                                    ActiveWindow.Close (True)
                                    
                                     Application.Calculation = xlCalculationAutomatic
                                     
                                            Application.ScreenUpdating = True
                                            
                                            MsgBox "Your Car Park has been submitted"
                                    
                                    Unload Me
                            End If
                    End If
            End If
    End If
End If
Dim LastRow As Long
Dim i As Long
ActiveWorkbook.Sheets("Sheet1").Activate
With ActiveSheet
       
   LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
   For i = LastRow To 2 Step -1
               
   If .Cells(i, "E").Value = 0 Then
   .Rows(i).Delete
   End If
   Next i
       
   End With
   
   With Application
   .Calculation = xlCalculationAutomatic 'Dont think i need this line
End With

End Sub

Many Thanks
 
Upvote 0
Does it work with the change?

Which workbook do you want to remove the line from? Is it the workbook the userform/code is in?
 
Upvote 0
The info is in a differerent workbook The line below in the code is where the file is

"'Workbooks.Open Filename:="C:\Users\MAZZA\Documents\Gavin\Car Park\FYVData.xls"

The VB runs but stops on the active. etc..

The Sheet the info needs to be removed from in "Sheet1" in FYVData.xls

Cheers
 
Upvote 0
So why do you close that workbook earlier in the code?

Is that meant to happen?

Also is it definitely only one row of data you want to remove/delete?

Sorry for another edit, but I thought you wanted to delete a row based on the value in column E of that row matching what's in a textbox on your userform?

The code you posted seems to be for deleting every row where there is a 0 in column E.
 
Last edited:
Upvote 0
Sorry,

Just reading through the code again I have asked the book to close before it removes the line of data a mistake on my part.

Also i need the row deleting that has the value in column "D".
The value it needs to be matched against is in "TextBox18".

The code i have posted was given to me by someone else who uses it to delete all rows with 0 in another column.
I only need to to cancel 1.

Many Thanks
 
Upvote 0
Try this.
Rich (BB code): 
Option Explicit
Private Sub CommandButton3_Click()
Dim wbFVYData As Workbook
Dim ws As Worksheet
Dim rngFnd As Range
Dim LastRow As Long
Dim I As Long
Dim iRow As Long

    'Checks if info in fields  are missing
    If Me.ComboBox4.Value = "" Then
        MsgBox "You need to complete the customer name"
        Exit Sub
    End If

    If Me.TextBox17.Value = "" Then
        MsgBox "You need to complete your question catogery"
        Exit Sub
    End If

    If Me.JEN.Value = "" Then
        MsgBox "Can you confirm you have checked the How Guide"
        Exit Sub
    End If
 
    If Me.CBoxAdd.Value = "" Then
        MsgBox "You need to complete your question"
        Exit Sub
    End If
 
    Application.ScreenUpdating = False

    Set wbFVYData = Workbooks.Open(Filename:="\\W2K6082\COMMON\SHARED\Gavin Mazza\Car Park\FYVData.xls")
 
    If wbFVYData.ReadOnly Then
        MsgBox "This file is being used by someone else please try again in a minute"
        wbFVYData.Close False
    Else
      
        Set ws = wbFVYData.Worksheets(CmbCSM.Value)

        ws.AutoFilterMode = False

        ws.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 19).Value = Array(txtdate.Value, _
                                                                                    TextBox18.Value, CmbCSM.Value, TextBox1.Value, TextBox2.Value, TextBox6.Value, TextBox5.Value, _
                                                                                    TextBox3.Value, TextBox4.Value, TextBox7.Value, TextBox8.Value, TextBox14.Value, TextBox15.Value, _
                                                                                    TextBox9.Value, JEN.Value, TextBox12.Value, ComboBox4.Value, TextBox17.Value, CBoxAdd.Value)
 
        With wbFVYData.Sheets("Sheet1")
            LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
            Set rngFnd = .Range("E2:E" & LastRow).Find(What:=Me.TextBox18.Value)
        End With
 
        If Not rngFnd Is Nothing Then
            rngFnd.EntireRow.Delete
        Else
            MsgBox "Not Found"
        End If
 
        wbFVYData.Close True
 
        'clear the data - not really needed, after you unload a form and then show it again all the controls will be reset to their defaults eg ""
        '            Me.txtdate.Value = ""
        '            Me.CmbCSM.Value = ""
        '            Me.TextBox1.Value = ""
        '            Me.TextBox2.Value = ""
        '            Me.TextBox6.Value = ""
        '            Me.TextBox3.Value = ""
        '            Me.TextBox5.Value = ""
        '            Me.TextBox4.Value = ""
        '            Me.TextBox7.Value = ""
        '            Me.TextBox8.Value = ""
        '            Me.TextBox14.Value = ""
        '            Me.TextBox9.Value = ""
        '            Me.JEN.Value = ""
        '            Me.TextBox12.Value = ""
        '            Me.ComboBox4.Value = ""
        '            Me.TextBox17.Value = ""
        '            Me.CBoxAdd.Value = ""
  
       MsgBox "Your Car Park has been submitted"
        Unload Me
  
    End If
 
    Application.ScreenUpdating = True
 
End Sub
 
Upvote 0
Good Evening,

Many Thanks, the vb works i gave you some wrong column numbers, but i realised and it works spot on.

Cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Correct sytax for yes / no Msgbox
2
Replies
10
Views
427
My Aswer Is This
My Aswer Is This
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
A
  • Question Question
Help Debugging Custom Data Label Macro
Replies
0
Views
521
arndtea
A
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
959
MoonDancer
M
A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
581
Aurum1
A

Forum statistics

Threads
1,224,534
Messages
6,179,390
Members
452,909
Latest member
VickiS

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