Highlight rows if cell equals Zero.

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
722
Hello friends,

I have a sheet with data, I want the below macro to go thru all the cells of column AF. If it finds 0.00 in the cells of AF, then I want the entire row to be highlighted (selected).

Please help me fill the question marks in the below code. And see if please see if I missed anything in the code.

Thanks,
Nawaf

Rich (BB code):
Sub Macro1()
    Dim Curr_Cell As Long
    Dim Prev_Cell As Long
    Dim i As Long
    Dim Loop_Count As Long
    
    ActiveWindow.Activate
    Loop_Count = Range(Selection, Selection.End(xlDown)).Count
    'MsgBox Loop_Count
    For i = 2 To Loop_Count
        Range("AF" & i).Select
        Curr_Cell = ActiveWindow.ActiveCell
        'MsgBox Curr_Cell
        If (Curr_Cell <> Prev_Cell) Then
        ??????
        End If
        Prev_Cell = Curr_Cell
       Next i
End Sub
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019
Just use conditional formatting. Select Formula and =AND($AF1=0,$AF1<>"") (assuming the active cell is in row 1).

edited to make column absolute and to handle blank cells.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
What are you actually trying to do?

ie why are you highlighting/selecting the rows?

I think there could be other ways of doing this without having to loop.
 

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
722
I am selecting the rows to enable me to see what I want to delete, ie I will delete all the rows with 0.00. Conditional formatting will only show me what needs to be deleted, but the code that I am working on will ease the deleting process.

Your help is appreciated.

Nawaf
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Nawaf

I'm sorry but I don't understand.:eek:

Why do you need to highlight/select the rows?

Can't you just delete them as you go along?
 

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
722
Thanks Norie for replying,

It would take a long time since I have data in over 3000 rows, once the rows are highlighted/selected it would be easy for me to delete.

Right click and delete.

Your help is highly regarded,
Nawaf
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,019

ADVERTISEMENT

Maybe I'm just dumber'n the average bear...for I would simply either (a) use AutoFilter or (b) Sort Decending, do a FIND on column AF for 0 and delete from there down. If I needed to restore original order or something then I might drag VBA into it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,304
Office Version
  1. 365
Platform
  1. Windows
Nawaf

Are you sure that's the case?

If speed is an issue you should be avoiding Select in the first place.

You might also want to look into using Autofilter.

By the way I don't see anything in the posted code that checks for 0.00.:eek:
 

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
722
It is funny how sometimes I complicate things, You guys are right I guess I don't need VBA after all, Greg/Norie you are right.

I will sort the data instead.

Thank you all.
Nawaf
 

Forum statistics

Threads
1,141,757
Messages
5,708,349
Members
421,566
Latest member
7Nabisco

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
Top