excel vba clear cell value with if contain specific cell value

jhonatan321 · Jul 14, 2021

Good evening everyone, I'm looking for some code in vba that can find specified dates in a line, if the date is not the same I need to delete the 3 columns, I want to leave only dates in the line that correspond to date1 and date 2 as in the example
before

later

Peter_SSs · Jul 15, 2021

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are and do not have to manually type out sample data. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Try this with a copy of your workbook.

VBA Code:

Sub Clear_Data()
  Dim c As Long
  
  For c = Cells(2, Columns.Count).End(xlToLeft).Column - 2 To 3 Step -3
    With Cells(2, c)
      If IsDate(.Value) And Cells(2, 1).Value <> .Value And Cells(2, 2).Value <> .Value Then .Resize(, 3).ClearContents
    End With
  Next c
End Sub

Crystalyzer · Jul 15, 2021

@Peter_SSs your solution is very elegant!! Mine is a bit rougher, but I allow for multiple rows.

@*Jhonatan321 as Peter suggests try the below on a copy of your workbook

VBA Code:

Sub RemoveData()
    Dim i As Integer
    Dim j As Integer
    Dim D1 As Date
    Dim D2 As Date
    Dim a As Range
    Dim LR As Long
    Dim LC As Long
    
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    
   For i = 2 To LR
        D1 = Cells(i, 1).Value
        D2 = Cells(i, 2).Value
        For j = 3 To LC - 2
            If Cells(i, j).Value <> D1 Then
                If Cells(i, j).Value <> D2 Then
                    Range(Cells(i, j), Cells(i, j + 2)).ClearContents
                End If
            End If
            j = j + 2
        Next j
    Next i
                
End Sub

jhonatan321 · Jul 15, 2021

Crystalyzer said:

@Peter_SSs your solution is very elegant!! Mine is a bit rougher, but I allow for multiple rows.

@*Jhonatan321 as Peter suggests try the below on a copy of your workbook

VBA Code:

Sub RemoveData()
    Dim i As Integer
    Dim j As Integer
    Dim D1 As Date
    Dim D2 As Date
    Dim a As Range
    Dim LR As Long
    Dim LC As Long
   
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
   
   For i = 2 To LR
        D1 = Cells(i, 1).Value
        D2 = Cells(i, 2).Value
        For j = 3 To LC - 2
            If Cells(i, j).Value <> D1 Then
                If Cells(i, j).Value <> D2 Then
                    Range(Cells(i, j), Cells(i, j + 2)).ClearContents
                End If
            End If
            j = j + 2
        Next j
    Next i
               
End Sub

Thank you very much Crystallizer, it worked perfectly, Some heroes wear capes, mine knows how to program very well in VBA, well my next challenge in vba I hope you can help me on this one

now that I have only the date and time specified in this line, I have added a column in cell B1 "time 1" and in cell E1 "time 2" and next to the blank fields close time 1, close time 2 and to the right of them close value 1, close value 2

Below is what I need to happen

1) check in this line all the same dates A2 and E2 and bring the time closest to B2 and paste in cell C2 and time closest to F2 paste in cell G2

2) Bring the closest time in this line starting at I2, the criteria of this time must be less than or equal, bring only the less than or equal time comparing the specified dates

3) Bring the cell next to the nearest time to cell D2 and G2

follow an example of before and after

116cf2a5-5a85-409d-b405-ef7a2df01b44 (1).xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

day 1

hora 1

close time 1

close value 1

day 1

hora 2

close time 2

close value 2

entry_date

hora

old_bin_id

entry_date

hora

old_bin_id

entry_date

hora

old_bin_id

entry_date

hora

old_bin_id

2

18/06/2021