mercredi 24 novembre 2021

Compare cell with a previous one in the same column- VBA

I have the data in excel like this, one column:

10/15/2021  7:59:42 AM
10/15/2021  7:59:44 AM
10/15/2021  7:59:46 AM
.
.
.
10/16/2021  7:59:42 AM
10/16/2021  7:59:48 AM
10/16/2021  7:59:49 AM

I used this code in VBA to create two columns, one with date and one with time:

Sub CommandButton1_Click()
    Dim rng As Range
    Set rng = [A1]
    Set rng = Range(rng, Cells(Rows.Count, rng.Column).End(xlUp))

    rng.Texttocolumns Destination:=[B1], DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, xlMDYFormat), Array(2, xlMDYFormat), Array(3, xlGeneralFormat)), _
    TrailingMinusNumbers:=True
    
    Columns("C").Delete
    Columns("E").Delete
    Columns("D").Delete
    
End Sub

After that, I have two columns. My goal is to have one column with date and time, but to have date only for the first time when it appears. I have done this with excel using IF and later CONCAT. I copied first row and for the second I used:

IF(B3=B2,"",B3)

The result is empty cell, I just then use CONCAT with row which contains time. I would love to do this in VBA, but I am not sure how.

Aucun commentaire:

Enregistrer un commentaire