ActiveCell alternative

[th081]

Hi All,

I have some code as below which i am using several times in my macro which causes flicker as the screen goes to the sheets back and forth (even though i am using screen updating as false ) how can i achive the same thing without using Activecell and selecting the sheet?

Sheets("D_Sheet").Select

Range("A1").Select

Do Until ActiveCell.Value = ""

If ActiveCell.Offset.Value = ActiveCell.Offset(1, 0).Value And ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(1, 3).Value Then
ActiveCell.Offset(1, 0).Select
Rows(ActiveCell.Row).EntireRow.Delete
End If

If ActiveCell.Offset(0, 3).Value <> ActiveCell.Offset(1, 3).Value Or ActiveCell.Offset(0, 1).Value <> ActiveCell.Offset(1, 1).Value Then
ActiveCell.Offset(1, 0).Select
End If
Loop

 

[Joe4]

Rather than trying to "guess" or try to reverse engineer you code, I think it would be more helpful if you could post a small sample of your data, and just explain (in plain English) exactly what it is you want your code to do. 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. 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.

 

[th081]

thanks Joe4,

the code is simply looping down column A and deleting the row where the values in two columns in that row match the values in the two columns in the row underneath.

the code works my question is do i have to select the sheet to do the code or is there an alternative.

 

[Joe4]

So, it looks like you are comparing columns A and D, is that correct?
Does it matter which row is deleted (the first or second of the matching rows)?

 

[th081]

That's right columns A and D, no it does not matter which one is deleted.

 

[Joe4]

To answer your question, yes, you should always try to avoid "Select" statements in your VBA code.
They usually are not necessary and will slow down your code.

When deleting or inserting rows via a loop, it is often easier to work backwards, from the bottom up. Then you do not need your second "if/then" to check to see if you can move to the next row or not.

If it is OK to delete the second row of the duplicate (and not the first), I believe this code will do what you want:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long

    Application.ScreenUpdating = False
 
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
 
'   Loop through all rows backwards, starting from last row
    For r = lr To 2 Step -1
'       Check for duplicates
        If Cells(r, "A") = Cells(r - 1, "A") And Cells(r, "D") = Cells(r - 1, "D") Then
            Rows(r).Delete
        End If
    Next r
 
    Application.ScreenUpdating = True
 
End Sub

 

[th081]

This code is not referencing a sheet, i.e it assumes it's the current sheet which it would not be?

 

[Joe4]

If you want to specify the sheet, you can do it like this:

Sub MyDeleteRows()

    Dim lr As Long
    Dim r As Long

    Application.ScreenUpdating = False
    
'   Run against D_Sheet
    With Sheets("D_Sheet")

'       Find last row in column A with data
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    
'       Loop through all rows backwards, starting from last row
        For r = lr To 2 Step -1
'           Check for duplicates
            If .Cells(r, "A") = .Cells(r - 1, "A") And .Cells(r, "D") = .Cells(r - 1, "D") Then
                .Rows(r).Delete
            End If
        Next r
    
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[th081]

Thank you, that looks perfect. I will try and confirm back.