Search another column for the same value and then skip to the nearest cell

[Laurence D]

Hi there,

I have some code that is speeding up some checks that I do every week over 1000 rows etc. I am just after a little help in speeding up my checks as this is tedious work. What I am after is for a macro to check if the the previous column (i.e. Column R) has the same value in it and and then if it does search the sequential column (column S) to identify whether there are any of those rows are empty (the red down below symbolises empty rows). if they are empty move the active cell to the nearest cell. If not empty then just move down to the nearest empty cell in that row (skipping hidden cells).

I have most of the code flying around I am just getting stuck on how to search for the same value in the previous column. Here is a table trying to illustrate what I am after

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]S[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]201901208[/TD]
[TD]Where it should go when working[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]201901208[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]201901208[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]201901208[/TD]
[TD]Where the macro started[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]201955515[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]201955515[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]201955515[/TD]
[TD]If S1 is not empty then skip to this cell[/TD]
[/TR]
</tbody>[/TABLE]


This is the code I have so far

Sub Selection_Completed()
   
   Application.ScreenUpdating = False
   
    If IsEmpty(ActiveCell) = False Then
           Do Until IsEmpty(ActiveCell)
          ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub
    End If
   
   Dim rng As range, cell As range
   Set rng = Selection
   For Each cell In rng
        cell.Value = "Completed"
   Next cell
 
    If IsEmpty(ActiveCell.Offset(1, 0)) = True Then
        ActiveCell.Offset(1, 0).Select
    Do Until ActiveCell.EntireRow.Hidden = False
        ActiveCell.Offset(1, 0).Select
    Loop
    End if

    Application.ScreenUpdating = True
   
End Sub

Thanks for the help, Laurence

 

[James006]

Hi,

Not sure to fully understand your constraints ... :wink:

However ... given your number of rows ... there is an obvious recommendation to speed up your whole process :

Forget about your loops ... and use Filter ...

Hope this will help