How to stop this macro if I come across an empty cell

Sarath Karanam

New Member
Joined
Mar 14, 2013
Messages
41
Hi,

I'm using a macro which is taking too long to run because of the number of cells it has to check.

I tried modifying it with no luck.

Code:
Sub dup()    Dim cell As Range, cella As Range, rng As Range, srng As Range
    Set rng = Sheets(2).Range("A2:A20000")
    Set srng = Sheets(1).Range("A2:A20000")
     
    For Each cell In rng
        For Each cella In srng
            If cella = cell And cella <> Empty Then
                cella.Interior.ColorIndex = 22
            End If
        Next cella
    Next cell
End Sub

Though I added "cella <> Empty" to this, it still is taking long as it has to check all the 20000 cells.

I want to stop this macro if "cella = Empty". However, I'm not able to figure a way to do that.

Please help.


Regards,
Sarath Karanam
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You don't need two loops. Try:

Code:
Sub dup()
    Dim cell As Range, cella As Range, rng As Range, srng As Range
    Set rng = Sheets(2).Range("A2:A20000")
    Set srng = Sheets(1).Range("A2:A20000")
    For Each cella In srng
        If cella <> "" Then
            If WorksheetFunction.CountIf(rng, cella) Then
                cella.Interior.ColorIndex = 22
            End If
        End If
    Next cella
End Sub
 
Upvote 0
Hi,

I'm presuming here that the empty cell will be the last cell in the column in which case you could just select the range and colour it.

Code:
Sub colourMe()


Dim lastRow As Long


Sheets(1).Select


lastRow = ActiveSheet.UsedRange.Rows.Count


Range("A2:A" & lastRow).Select
    With Selection
        .Interior.ColorIndex = 22
    End With
    
Sheets(2).Select


lastRow = ActiveSheet.UsedRange.Rows.Count


Range("A2:A" & lastRow).Select
    With Selection
        .Interior.ColorIndex = 22
    End With
End Sub
 
Upvote 0
Hi Andrew,

Thank you very much. It works perfect.

However, I'm not able to figure out "If WorksheetFunction.CountIf(rng, cella)" part. I'm a novice in VBA.



Regards,
Sarath Karanam

You don't need two loops. Try:

Code:
Sub dup()
    Dim cell As Range, cella As Range, rng As Range, srng As Range
    Set rng = Sheets(2).Range("A2:A20000")
    Set srng = Sheets(1).Range("A2:A20000")
    For Each cella In srng
        If cella <> "" Then
            If WorksheetFunction.CountIf(rng, cella) Then
                cella.Interior.ColorIndex = 22
            End If
        End If
    Next cella
End Sub
 
Upvote 0
Hi ArthriticPanda,

Thank you for the help. As you said, the empty cell is the last one. However, the range keeps on changing frequently.

I tried using this. It highlighted all the non empty cells from the column A in both the Sheets 1 & 2, though they're not matching.


Regards,
Sarath Karanam

Hi,

I'm presuming here that the empty cell will be the last cell in the column in which case you could just select the range and colour it.

Code:
Sub colourMe()


Dim lastRow As Long


Sheets(1).Select


lastRow = ActiveSheet.UsedRange.Rows.Count


Range("A2:A" & lastRow).Select
    With Selection
        .Interior.ColorIndex = 22
    End With
    
Sheets(2).Select


lastRow = ActiveSheet.UsedRange.Rows.Count


Range("A2:A" & lastRow).Select
    With Selection
        .Interior.ColorIndex = 22
    End With
End Sub
 
Last edited:
Upvote 0
Apologies Sarath, I neglected to add the part where the code matches the cells.

Andrew's solution will be the way to go on this one.

AP.
 
Last edited:
Upvote 0
Just for your own interest, here is the complete code using slightly different VBA techniques.

Code:
Sub colourMe()

Dim lastRow As Long

Application.ScreenUpdating = False

Sheets(2).Select
lastRow = ActiveSheet.UsedRange.Rows.Count
Sheets(1).Select

For i = 2 To lastRow
If Cells(i, 1) = Sheets(2).Cells(i, 1) Then
Cells(i, 1).Select
    With Selection
        .Interior.ColorIndex = 22
    End With
End If
Next i


End Sub

Regards,

...and good luck with VBA! ;)
 
Upvote 0
It's using the COUNTIF worksheet function. On a worksheet it would be something like:

=COUNTIF(Sheet2!A2:A2000,Sheet1!A2)>0
 
Upvote 0
Thanks a lot AP :)

Just for your own interest, here is the complete code using slightly different VBA techniques.

Code:
Sub colourMe()

Dim lastRow As Long

Application.ScreenUpdating = False

Sheets(2).Select
lastRow = ActiveSheet.UsedRange.Rows.Count
Sheets(1).Select

For i = 2 To lastRow
If Cells(i, 1) = Sheets(2).Cells(i, 1) Then
Cells(i, 1).Select
    With Selection
        .Interior.ColorIndex = 22
    End With
End If
Next i


End Sub

Regards,

...and good luck with VBA! ;)
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top