|
|
|
|
New Member
|
|
Dec 22, 2011, 11:03 AM
|
|
Macro to select a value in the range of cells?
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!
|
|
|
Software Expert
|
|
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?
|
|
|
New Member
|
|
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.
|
|
|
Software Expert
|
|
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)
|
|
|
Software Expert
|
|
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.
Code:
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
|
|
|
New Member
|
|
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
|
|
|
Software Expert
|
|
Dec 26, 2011, 11:32 PM
|
|
This is what your macro should read as to work at top efficiency:
Code:
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
|
|
|
New Member
|
|
Dec 28, 2011, 03:56 AM
|
|
Thanks again J Beaucaire :)
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Macro - select range based on cell data
[ 4 Answers ]
I need a macro that will select a range based on a number in a cell. If the number in the cell (in this case the cell is K1) is 200 then I want to select Range A2:D201, if it was 100 then select Range A2:D101.
Thanks for the help!
View more questions
Search
|