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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
  • Question Question
How to "operate" with a cell in a previous worksheet?
Replies
3
Views
77
abdelfattah
A
T
  • Solved
Find #REF! errors in multiple tables and delete those rows
Replies
7
Views
310
Peter_SSs
Peter_SSs
J
  • Question Question
Conditional Formatting with deleting rows
Replies
1
Views
421
6StringJazzer
6StringJazzer
C
  • Solved
VBA code for changes in cell and delete cell value
2
Replies
10
Views
293
cooleomter
C
M
  • Solved
VBA to delete cells from columns preceding the lowest value in the column
Replies
5
Views
116
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,823
Messages
6,181,184
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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