How to delete a value from a cell in VBA

K

kev_bez

New Member
Joined
Jul 20, 2023
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Good afternoon all, Newbie here trying to make some code that I have copied work. Everything works apart from it doesn't delete the value from the cell of the last selection. It deletes all the previous ones. This is the code

Sub PrintCWO()
If Not ActiveSheet.Name Like "WK*" Then
MsgBox "Ensure correct worksheet selected"
Exit Sub

Application.ScreenUpdating = False

Else:
Count = 1
For i = 4 To 500

If LCase(Range("BB" & i).Value) = "y" Then

Area = Range("A" & i).Value
Zone = Range("B" & i).Value
Station = Range("C" & i).Value
Robot = Range("D" & i).Value
Desc = Range("E" & i).Value
Ref = Range("F" & i).Value
ReqDate = Range("G" & i).Value
Requestor = Range("H" & i).Value
Trades = Range("I" & i).Value
Gap = Range("J" & i).Value
Stoppage = Range("K" & i).Value
Priority = Range("L" & i).Value
Order = Range("N" & i).Value
Est = Range("T" & i).Value
Authorised = Range("BA" & i).Value

Sheets("CWO").Range("B1").Value = Area
Sheets("CWO").Range("C1").Value = Zone
Sheets("CWO").Range("D1").Value = Station
Sheets("CWO").Range("E1").Value = Robot
Sheets("CWO").Range("F1").Value = Desc
Sheets("CWO").Range("G1").Value = Ref
Sheets("CWO").Range("H1").Value = ReqDate
Sheets("CWO").Range("I1").Value = Requestor
Sheets("CWO").Range("J1").Value = Trades
Sheets("CWO").Range("K1").Value = Gap
Sheets("CWO").Range("L1").Value = Stoppage
Sheets("CWO").Range("M1").Value = Priority
Sheets("CWO").Range("O1").Value = Order
Sheets("CWO").Range("U1").Value = Est
Sheets("CWO").Range("V1").Value = Authorised

If Count = 1 Then
x = ActiveSheet.Name
Sheets("CWO").Activate
Application.Dialogs(xlDialogPrint).Show
Sheets(x).Activate
Else: Sheets("CWO").PrintOut
End If

Count = Count + 1
Range("BB" & i).Value = ""

End If

Next

End If

Application.ScreenUpdating = True
End Sub

Can anybody help please?
Thank you!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
The only cell I see having its value deleted is column BB:
VBA Code: 
Range("BB" & i).Value = ""
So which columns are supposed to be cleared out?
 
Upvote 0
Joe4 said:
The only cell I see having its value deleted is column BB:
VBA Code: 
Range("BB" & i).Value = ""
So which columns are supposed to be cleared out?
Click to expand...
Hi Yes I need the value in cell BB cleared out.

Essentially this code scans each row looking for a "Y" in BB and any rows that have a "Y" will be copied and printed out. I then want the "Y" to be cleared out of the cell once printed.
If for example there are 5 rows with a "Y", it clears the first four and leaves the last one. I need it to clear out all the "Y"s in cell BB
Hope this makes sense.
Thanks
 
Upvote 0
Maybe try moving that line above the IF block, i.e.
Rich (BB code): 
                Range("BB" & i).Value = ""

                If Count = 1 Then
                    x = ActiveSheet.Name
                    Sheets("CWO").Activate
                    Application.Dialogs(xlDialogPrint).Show
                    Sheets(x).Activate
                Else: Sheets("CWO").PrintOut
                End If

                Count = Count + 1

Also, a word of advice. NEVER use reserved words (words already used for functions, properties, methods, etc) like "Count" as variable, procedure, or function names.
Doing so can cause errors and unexpected results.
 
Upvote 0
Are u sure to clear the cells in the right worksheet?
VBA Code: 
NameOfActiveSheet.Range("BB" & i).Value = ""
 
Upvote 0
Joe4 said:
Maybe try moving that line above the IF block, i.e.
Rich (BB code): 
                Range("BB" & i).Value = ""

                If Count = 1 Then
                    x = ActiveSheet.Name
                    Sheets("CWO").Activate
                    Application.Dialogs(xlDialogPrint).Show
                    Sheets(x).Activate
                Else: Sheets("CWO").PrintOut
                End If

                Count = Count + 1

Also, a word of advice. NEVER use reserved words (words already used for functions, properties, methods, etc) like "Count" as variable, procedure, or function names.
Doing so can cause errors and unexpected results.
Click to expand...
No unfortunately this doesn't work. Thanks for the advice, maybe it is the Count function causing the problem?
 
Upvote 0
I tried cleaning-up/simplifying/steamlining your VBA code.

Try this:
VBA Code: 
Sub PrintCWO()

    Dim lr As Long
    Dim ct As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long
    
    
    If Not ActiveSheet.Name Like "WK*" Then
        MsgBox "Ensure correct worksheet selected"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False

'   Set Worksheet object variables
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("CWO")

'   Find last row in column BB with data
    lr = ws1.Cells(ws1.Rows.Count, "BB").End(xlUp).Row

    ct = 1
    
'   Loop through all rows
    For i = 4 To lr
    
        If LCase(ws1.Range("BB" & i).Value) = "y" Then

            With ws2
                .Range("B1").Value = ws1.Range("A" & i).Value
                .Range("C1").Value = ws1.Range("B" & i).Value
                .Range("D1").Value = ws1.Range("C" & i).Value
                .Range("E1").Value = ws1.Range("D" & i).Value
                .Range("F1").Value = ws1.Range("E" & i).Value
                .Range("G1").Value = ws1.Range("F" & i).Value
                .Range("H1").Value = ws1.Range("G" & i).Value
                .Range("I1").Value = ws1.Range("H" & i).Value
                .Range("J1").Value = ws1.Range("I" & i).Value
                .Range("K1").Value = ws1.Range("J" & i).Value
                .Range("L1").Value = ws1.Range("K" & i).Value
                .Range("M1").Value = ws1.Range("L" & i).Value
                .Range("O1").Value = ws1.Range("N" & i).Value
                .Range("U1").Value = ws1.Range("T" & i).Value
                .Range("V1").Value = ws1.Range("BA" & i).Value
            End With

            ws1.Range("BB" & i).Value = ""
                
            If ct = 1 Then
                ws2.Activate
                Application.Dialogs(xlDialogPrint).Show
                ws1.Activate
            Else: ws2.PrintOut
            End If

            ct = ct + 1

        End If

    Next

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
End Sub
 
Upvote 1
Solution
Joe4 said:
I tried cleaning-up/simplifying/steamlining your VBA code.

Try this:
VBA Code: 
Sub PrintCWO()

    Dim lr As Long
    Dim ct As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long
   
   
    If Not ActiveSheet.Name Like "WK*" Then
        MsgBox "Ensure correct worksheet selected"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.EnableEvents = False

'   Set Worksheet object variables
    Set ws1 = ActiveSheet
    Set ws2 = Sheets("CWO")

'   Find last row in column BB with data
    lr = ws1.Cells(ws1.Rows.Count, "BB").End(xlUp).Row

    ct = 1
   
'   Loop through all rows
    For i = 4 To lr
   
        If LCase(ws1.Range("BB" & i).Value) = "y" Then

            With ws2
                .Range("B1").Value = ws1.Range("A" & i).Value
                .Range("C1").Value = ws1.Range("B" & i).Value
                .Range("D1").Value = ws1.Range("C" & i).Value
                .Range("E1").Value = ws1.Range("D" & i).Value
                .Range("F1").Value = ws1.Range("E" & i).Value
                .Range("G1").Value = ws1.Range("F" & i).Value
                .Range("H1").Value = ws1.Range("G" & i).Value
                .Range("I1").Value = ws1.Range("H" & i).Value
                .Range("J1").Value = ws1.Range("I" & i).Value
                .Range("K1").Value = ws1.Range("J" & i).Value
                .Range("L1").Value = ws1.Range("K" & i).Value
                .Range("M1").Value = ws1.Range("L" & i).Value
                .Range("O1").Value = ws1.Range("N" & i).Value
                .Range("U1").Value = ws1.Range("T" & i).Value
                .Range("V1").Value = ws1.Range("BA" & i).Value
            End With

            ws1.Range("BB" & i).Value = ""
               
            If ct = 1 Then
                ws2.Activate
                Application.Dialogs(xlDialogPrint).Show
                ws1.Activate
            Else: ws2.PrintOut
            End If

            ct = ct + 1

        End If

    Next

    Application.ScreenUpdating = True
    Application.EnableEvents = True
   
End Sub
Click to expand...
WOW, Thank you very much! This has worked perfectly! Genius level!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Replacing delete with a value
Replies
3
Views
166
Alex Blakenburg
A
L
  • Question
vba based on cell value to open another workbook and paste data
Replies
3
Views
97
kevin9999
K
J
  • Question
VBA to make Sheet Name = Cell Value
2
Replies
15
Views
530
Joe4
Joe4
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
188
DanteAmor
DanteAmor
J
  • Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
216
jmk15315
J

Forum statistics

Threads
1,215,071
Messages
6,122,964
Members
449,094
Latest member
Anshu121

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