PDA

View Full Version : Macro to select a value in the range of cells?


Dilawer
Dec 22, 2011, 11:03 AM
I need a macro that will select every cell in a range based on a number in a cell. If the number in the cell (in this case the cell is b2,b5,b10 and b15) is 0 then I want to select cell b2,b5,b10,b15 in the range b1:b50

Thanks for the help!

JBeaucaire
Dec 22, 2011, 03:40 PM
Select them, then do what? VBA is pretty powerful, you don't actually have toe "select" anything to process the found cells. I can show you a quick macro for selecting, but if you tell me the actual end-goal, I can just have VBA do that with the found cells.

Also, you are wanting cells that have the actual number 0, not just cells that are zero value, including blanks?

Dilawer
Dec 22, 2011, 08:31 PM
Yes, I need to select only cells with zero value in the range b1:b50 then need to hide the Entire Row of selected cells.

thanks a ton.

JBeaucaire
Dec 23, 2011, 06:42 AM
So, a blank cell has a zero value, hide those?
A cell with the NUMBER zero, hide those?

(that's two separate questions)

JBeaucaire
Dec 23, 2011, 06:50 AM
Actually, here, you can adapt this. There are two "tests" in this macro, remove one if you only want the other, this will hide rows that are empty in B, or if they have a numeric value of zero in B.


Sub HideEm()
Dim Rw As Long
Application.ScreenUpdating = False

For Rw = 1 To 50
Rows(Rw).EntireRow.Hidden = Cells(Rw, "B") = "" Or Cells(Rw, "B") = 0
Next Rw

Application.ScreenUpdating = True
End Sub

Dilawer
Dec 26, 2011, 10:24 PM
thanks a ton J Beaucaire :)

your code really halpfull to making work the following code :)

I took "Application.ScreenUpdating = False" from your code and my code is now working perfectly

thanks again :)

Sub HideRows()
Application.ScreenUpdating = False
Range("b1:b50").Select
Selection.EntireRow.Hidden = False
For Each cell In ActiveSheet.Range("b1:b50")
If cell.Value = 0 Then cell.EntireRow.Hidden = True
Next
End Sub

JBeaucaire
Dec 26, 2011, 11:32 PM
This is what your macro should read as to work at top efficiency:

Sub HideRows()
Application.ScreenUpdating = False

For Each cell In ActiveSheet.Range("B1:B50")
cell.EntireRow.Hidden = cell.Value = 0
Next cell

Application.ScreenUpdating = True
End Sub

Dilawer
Dec 28, 2011, 03:56 AM
Thanks again J Beaucaire :)