Keep the Cell Blank if another cell is blank in the same row

[mamun_ges]

Hi, Everyone

Hope all are fine.

I am working on a worksheet with some data related to other cells. In this worksheet, if any cell in between W9:W1200 is empty then any data input in the same row of AC and AD will be cleared.

As before, if any cell between X9:X1200 is empty, then any data if input in the same row of AE and AF will be cleared.

Can it done with the VBA? If, please .....

I upload an image to better understand my thoughts.

Thanks in advance

 

[Joe4]

It will require VBA to do this. Are you agreeable to a VBA solution?

The next question then is on the timing of things. What is the order of how data is entered into these columns (W, AC:AD, X, AE:AF)?
Depending on how/when data is entered, we have be able to create VBA code which runs automatically upon data entry.
Otherwise, we would need to create VBA code which you would have to call/run manually.

 

[mamun_ges]

I would love to do this with a VBA solution. Thanks, Joe4 for the response.

AC: AD & AE: AF Data are input through the input box of a command button.

 

[Joe4]

I would love to do this with a VBA solution. Thanks Joe4 for the response.

OK, then I need you to answer the other questions I had in my original reply!

 

[spreadsheetman]

hey, I wrote a VBA code that should solve your problem:

[/
Public Sub spreadsheetman():
    For i = 1 To 100
        If IsEmpty(Range("W" & i)) Then
            Range("AC" & i).ClearContents
            Range("AD" & i).ClearContents
        End If
        If IsEmpty(Range("X" & i)) Then
            Range("AE" & i).ClearContents
            Range("AF" & i).ClearContents
        End If
    Next i
]

End Sub

 

[mamun_ges]

My Range of Data starts from 9 to 1200. So, do I need to change

For i = 1 To 100

.

 

[Joe4]

Here is code that you can run manually:

Sub MyClearValues()

    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Loop through rows 9-1200
    For r = 9 To 1200
'       See if column W is empty
        If Cells(r, "W") = "" Then
'           Clear columns AC:AD
            Range(Cells(r, "AC"), Cells(r, "AD")).ClearContents
        End If
'       See if column X is empty
        If Cells(r, "X") = "" Then
'           Clear columns AC:AD
            Range(Cells(r, "AE"), Cells(r, "AF")).ClearContents
        End If
    Next r
   
    Application.ScreenUpdating = True
   
    MsgBox "Macro complete!", vbOKOnly
   
End Sub

Note that since you are looping through over 1000 rows, the "Application.ScreenUpdating" lines in your code will help make your code run faster and avoid the annoying screen flickering.

 

[spreadsheetman]

Ah yes exactly, I was just

My Range of Data starts from 9 to 1200. So, do

My Range of Data starts from 9 to 1200. So, do I need to change

For i = 1 To 100

.

Yes, I forgot to change that for you

 

[mamun_ges]

I try your code but could not make it happen.
I am using the below code for data entry in AC-AF.

 Dim Popup As Integer
 Dim x(), r As Range, C As Range, dt
 Dim myCell As Range, myCell2 As Range
 
 Dim DataVal As String, DataVal2 As String
 Dim DataVal1 As Date, DataVal3 As Date
 
 'Select column I then run the code manually by command
 
 Popup = MsgBox("Press Yes for Enter VAT Data Entry", vbQuestion + vbYesNo + vbDefaultButton2, "VAT & AIT")
 If Popup = vbYes Then
         DataVal = InputBox("Please, Enter VAT Number" _
         & vbNewLine & "Press OK For Update, Cancel for Resume")
         DataVal1 = InputBox("Please, Enter VAT Date. ", "Date As DD/MM/YYYY", Default:=Date)
         dt = CDate(DataVal1)
         Range(Selection.Address).Offset(0, 20) = DataVal
         Range(Selection.Address).Offset(0, 21) = dt
        
 Else

 End If
 
 Popup = MsgBox("Press Yes for Enter AIT Data Entry", vbQuestion + vbYesNo + vbDefaultButton2, "VAT & AIT")
 If Popup = vbYes Then
         DataVal2 = InputBox("Please, Enter AIT Number" _
         & vbNewLine & "Press OK For Update, Cancel for Resume")
         DataVal3 = InputBox("Please, Enter AIT Date. ", "Date As DD/MM/YYYY", Default:=Date)
         dt = CDate(DataVal3)
         Range(Selection.Address).Offset(0, 22) = DataVal2
         Range(Selection.Address).Offset(0, 23) = dt
        
 Else
 Exit Sub
 End If

Here on selection of I column I run above code . If the "W or X" is empty then the correspondent offset value remains empty.

 

[Joe4]

I am confused.
How can you run the code "manually" in the middle of your other code? That really isn't possible.
You could call it and run it in the middle of your other code, but not run it manually.

In any event, you probably should not run the code I gave you until after you are done making edits to your data with your other code.