HIDE EMPTIES

[Guest]

A B C D E

1 Name submitted 15-mar 16-mar 17-mar 18-ma

2 joe <x out><x-out><x-out><x-out><x-out

3 Sched Hrs <x out> 12:00 17:00 12:00 17:0

4 total OT 10:00* 5:00 5:0

* = sum of c4 & d4

So, <x-out> simply meaning that those cells aren't used. lets pretend cell B4 is empty. I want to hide Rows 2-3-4. i will have about 100 names that i would like to do the same thing with. anyone have any thoughts

Thanks,
Stev

 

[Tom Ogilvy]

You still haven't stated what your criteria is for hiding the 3 rows.

The criteria you state: If B4 is empty

based on that

if isempty(Range("B4")) then
Range("2:4").EntireRow.Hidden = True
End if


But my best guess so far is that if the formula in column B of the third of
3 rows returns an empty string then hide the rows:

Dim rng as Range
for i = 2 to 301 step 3
set rng = cells(i+2,2)
if rng.Value = "" then
cells(i,1).resize(3).EntireRow.Hidden = True
end if
Next

This would require a formula like

=If(sum(C44)=0,"",Sum(C44))

to be in B4.

--
Regards,
Tom Ogilvy

A B C

D E F

1 Name submitted 15-mar 16-mar 17-mar 18-mar

2 joe <x out><x-out><x-out><x-out><x-out>

3 Sched Hrs <x out> 12:00 17:00 12:00 17:00

4 total OT 10:00* 5:00 5:00

* = sum of c4 & d4.

So, <x-out> simply meaning that those cells aren't used. lets pretend cell

B4 is empty. I want to hide Rows 2-3-4. i will have about 100 names that i
would like to do the same thing with. anyone have any thoughts?

 

[Guest]

SORY AGAIN TOM. IT IS B4. I'LL GIVE THIS A TRY. THANKS.

 

[Tom Ogilvy]

There is no "these"

in the VBE, insert => Module

paste in the code:

Sub HideStuff()
Dim rng As Range
For i = 2 To 301 Step 3
Set rng = Cells(i + 2, 2)
If rng.Value = "" Then
Cells(i, 1).Resize(3).EntireRow.Hidden = True
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Do these go in a vba module or do i simply insert them into them into the

"this worksheet" part?

 

[Tom Ogilvy]

Sub HideStuff()
Dim rng As Range
For i = 2 To 301 Step 3 ' <= the two says to start on row 2
Set rng = Cells(i + 2, 2) ' <= the +2 says to look two cells down
If rng.Value = "" Then
Cells(i, 1).Resize(3).EntireRow.Hidden = True
End If
Next
End Sub

if we start in 2, then i = 2 and i + 2 is 4, thus B4 cells(row,
column) column = 2

--
Regards,
Tom Ogilvy

Which part of this designates where start running. what tells it to start

looking at cell b4 rows 2,3,4. i now want to use this on another worksheet i
have. I am tweeking it but can't quite find the right way to do it.

 

[Tom Ogilvy]

Assume the first row to look at is Row 7 and you want to check column
3/column C of the third row in that group of 3:

Sub HideStuff()
Dim rng As Range
For i = 7 To 301 Step 3
Set rng = Cells(i + 2, 3)
If rng.Value = "" Then
Cells(i, 1).Resize(3).EntireRow.Hidden = True
End If
Next
End Sub

--
Regards,
Tom Ogilvy

Tom,

One more stupid question then I swear I will leave you alone for a while.

So if i wanted it to start @ C9. What do I change to make that work.