VB Script for excel 2010

[lsein8541]

Hi ! I have a 93,000 row spreadsheet, where by I want to check the cell in Column "C" for a 0. If the contents of that cell is 0 than I want to hide (or delete will work too) that entire row. So looking for some VB code that will go through a range, check the cell contacts of "C" and if 0 do the action.

Anyone know how to do that ?

If i can accomplish that, Can I also look at the contents of a different row, and if the value is a "Y" change the color of that row to any color ?

Thank you so much for any help !

Larry

 

[VoG]

Hi. Try this

Sub atest()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
    Rows(i).Hidden = Range("C" & i).Value = 0
Next i
Application.ScreenUpdating = True
End Sub

 

[lsein8541]

Thank you !

Do I need to put a range in (eg c1.c93000) ?

Trying this now...

 

[VoG]

No, it finds the range itself

 

[lsein8541]

1st... thank you for taking the time to help....

The macro runs with no errors, but it does not do any actions...

Thoughts ?

Thanks again

 

[VoG]

I've just tested it on a small range and it works for me.

Do the cells in column C contain 0 or are they blank? Or perhaps they contain numbers like 0.1 formatted to zero decimal places? Or numbers stored as text?

 

[lsein8541]

Woops... sorry.. user error, I move the column to "J" and didnt change it in both spots in the macro.

It works fine.. thank you...

So as long as I am asking for help...

Is there a similar VB that I can use to do two more things:

1) if Column "H" = Y than change the color of the row ?

and

2) if Column "E" = PCES-01 or PCES-02 to insert a row

Thanks again....

 

[VoG]

Try these. Note that the first one could be done using Conditional Formatting

Sub btest()
Dim LR As Long, i As Long
LR = Range("H" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To LR
    If Range("H" & i).Value = "Y" Then Rows(i).Interior.ColorIndex = 24
Next i
Application.ScreenUpdating = True
End Sub


Sub ctest()
Dim LR As Long, i As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For i = LR To 1 Step -1
    If Range("E" & i).Value = "PCES-01" Or Range("E" & i).Value = "PCES-02" Then Rows(i).Insert
Next i
Application.ScreenUpdating = True
End Sub

 

[lsein8541]

All work great, thank you so much for all your help...