Delete and change data based on multiple criteria in a variable range

Question

I asked a similar question before and someone helped me with another problem I had with this macro but now I got stuck again (1 week now). I wanted to make the macro variable in terms of range (row and column).

For a better understanding:

Every week I'll the latest added column and insert it between Q:R (only in this example, it's variable). Here is the macro I use to insert the column:

Sub insertColumn()

Dim lastrow         As Long
Dim LastCol         As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Copies the third last column and inserts it between the column [last date] and Overall'
With Sheets("getDATA")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    lastrow = .Cells(.Rows.Count, "G").End(xlUp).Offset(7, 0).Row
    .Columns(LastCol - 2).Copy
    .Columns(LastCol - 1).Insert Shift:=xlToRight
End With

With Sheets("getDATA")
    .Range("G7").End(xlToRight).Offset(0, -2).Value = Date
    .Range(Cells(8, LastCol).Address(), Cells(lastrow, LastCol).Address()).Offset(0, -1).Formula = "=IFNA(INDEX($D:$D,MATCH($L8,$E:$E,0)),"""")"
End With

With Sheets("getDATA")
    .Columns(LastCol - 2).PasteSpecial Paste:=xlPasteValues
End With

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

After inserting a column, I'll check the DATA. If something new gets added, I'll delete everything that was in the weeks before.

If the DATA has the same value one, two, three or more weeks, the macro should only keep the first one. If the cell is blank, the macro should add "Other" in the column Overall.

The overall column should also display the value, we kept. You can see this in the example table - everything that grey should be deleted.

Here is the Macro that checks the criteria:

Public Sub CheckDATA()

    Dim myRow           As Range
    Dim myCell          As Range
    Dim inputRange      As Range
    Dim previousCell    As Range
    Dim flagValue       As Boolean
    Dim lastCell        As Range
    Dim lastrow         As Long
    Dim LastCol         As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("getDATA")
    lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

    Set inputRange = Worksheets(1).Range(Cells(8, 13).Address(), Cells(lastrow, LastCol - 2).Address())
    For Each myRow In inputRange.Rows
        Set previousCell = Nothing
        flagValue = False
        For Each myCell In myRow.Cells
            If Len(myCell) Then flagValue = True
            If Not previousCell Is Nothing Then
                If previousCell <> myCell Then
                    previousCell.clear
                    Set previousCell = myCell
                Else
                    myCell.clear
                End If
            Else
                Set previousCell = myCell
            End If
            Set lastCell = myCell
        Next myCell

        If Not flagValue Then
            lastCell.Offset(0, 1) = "Other"
        Else
            lastCell.Offset(0, 1) = previousCell
        End If
    Next myRow

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I think the main problem appears in the line If previousCell <> myCell Then previousCell.clear. The macro works perfectly till I'll add a third week. The main mistake is that it ONLY checks the cell next to it instead of the entire row-range. This will cause the macro to delete every value that was there before, no matter the value was 3 weeks in a row the same. Because of this, I can't tell when the value first got inserted - hopefully this does make sense. Every "Value" is a sales order block. I want to know in which week the block first appeared

In Visual Basic Editor (VBE), try running your your macro using F8. This will execute your code line by line. Now before you press F8 again, open the Locals Window (you can find it under View). This will show you all the variables and its current value. Continuously press F8 to find out what line of code doesn't do what you expect. This way, you will be able to find out what went wrong and why you're not getting the desired result. Once you've found your specific issue, post it again here if you are unable to solve it. — L42
– L42, Commented Dec 20, 2017 at 7:32
I posted the issue. The macro recognized the variable range, but not when checking the cells value. It will only check two cells (of both weeks), not the entire row-range. — Bluesector
– Bluesector, Commented Dec 20, 2017 at 7:36
Yes, I understand, but I wouldn't be able to reproduce it since we do not have the data you're working on and there is no error, it just doesn't do what you want. What I'm asking is for you to help us help you by trimming down your issue instead of letting us set up a test data and validate your code. And using the Locals Window and stepping through each line of code will improve your coding skills more than you'll ever know. — L42
– L42, Commented Dec 20, 2017 at 7:40
Ok, that's plausible. I think the main problem appears in the line If previousCell <> myCell Then previousCell.clear. The macro works perfectly till I'll add a third week. The main mistake is that it ONLY checks the cell next to it instead of the entire row-range. This will cause the macro to delete every value that was there before, no matter the value was 3 weeks in a row the same. Because of this, I can't tell when the value first got inserted - hopefully this does make sense. Every "Value" is a sales order block. I want to know in which week the block first appeared. — Bluesector
– Bluesector, Commented Dec 20, 2017 at 7:51

Jochen · Accepted Answer · 2017-12-20 14:45:55Z

1

I would use a new collection for each row. You can add the value and iy you encounter an error (key already exists) then clear your cell. At the end you can use the last entered value to determine your 'overall'. Please take a look at the following code (I defined another range for testing, but you should be able to use the for..each with the lines before within your code):

Sub Test()
Set myrow = Range("A1:H1")
    Dim Col As Collection, dmy
    On Error Resume Next
    Set Col = New Collection
    For Each mycell In myrow.Cells
        Err.Clear
        Col.Add mycell.Value, mycell.Value
        If Err Then mycell.ClearContents
    Next mycell
    myrow.Cells(1, myrow.Cells.Count + 1).Value = Col(Col.Count)
End Sub

EDIT: If you only want to delete a value that is identical to the last value (and not any value in that row before, as I understood) then you dont need a colletcion for this. Just use a normal variable like in the following code. I don't think the code will delete other values than that...:

Sub Test2()
Dim CurVal$
Set myrow = Range("A1:H1")
    CurVal = ""
    For Each mycell In myrow.Cells
        If mycell.Value = CurVal Then
            mycell.ClearContents
        Else
            CurVal = mycell.Value
        End If
    Next mycell
    myrow.Cells(1, myrow.Cells.Count + 1).Value = iif(len(CurVal) > 0,curval,"other")
End Sub

edited Dec 20, 2017 at 14:45

answered Dec 20, 2017 at 8:56

Jochen

1,2541 gold badge7 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bluesector Over a year ago

Thank you! I tested the macro. Unfortunately the macro doesn't keep the old value. It only keeps the new added value, no matter if it's the same value.

Bluesector Over a year ago

Sorry, I tested it again:The only problem I can see here is, that it doesn't delete the old value, when adding a new and different value. It keeps the old with the new one. Also it should add in the column LastCol-1 "other" to the row-ranges that have no values in it.

Bluesector Over a year ago

Also: somehow when the macro deletes to many values: i.imgur.com/YytETJR.png

Collectives™ on Stack Overflow

Delete and change data based on multiple criteria in a variable range

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related