macro that hides rows

[plost33]

I have the following macrothat I just cannot seem to finish up. the code is suppost to hide all rows where the cell value in the range of B4:B49 is blank/empty/nothing. If nothing is in the cell then that row is hidden. but I am not sure whats wrong but it is just not working. here what I have so far:

Application.ScreenUpdating = False
Sheets("well access").Cells.EntireRow.Hidden = False
Worksheets("well access").Activate
With ActiveSheet
cnt1 = WorksheetFunction.CountIf(.Range("B4:B49"), "")
End With
Sheets("well intervention").Cells.EntireRow.Hidden = False
Worksheets("well intervention").Activate
With ActiveSheet
cnt2 = WorksheetFunction.CountIf(.Range("B4:B49"), "")
End With
Sheets("TA").Cells.EntireRow.Hidden = False
Worksheets("TA").Activate
With ActiveSheet
cnt3 = WorksheetFunction.CountIf(.Range("B4:B49"), "")
End With
Sheets("PA").Cells.EntireRow.Hidden = False
Worksheets("PA").Activate
With ActiveSheet
cnt4 = WorksheetFunction.CountIf(.Range("B4:B49"), "")
End With
Sheets("Non Productive").Cells.EntireRow.Hidden = False
Worksheets("Non Productive").Activate
With ActiveSheet
cnt5 = WorksheetFunction.CountIf(.Range("B4:B49"), "")
End With
Worksheets("code Totals (hidden)").Activate
cnt = WorksheetFunction.Sum(cnt1 + cnt2 + cnt3 + cnt4 + cnt5)
 
For S = 1 To 5 '1 to number of sheets, eg 5 in this example
Select Case S
Case 1: ShtName = "Well Access" 'Type the names of each sheet (in "")
Case 2: ShtName = "Well Intervention"
Case 3: ShtName = "TA"
Case 4: ShtName = "PA"
Case 5: ShtName = "Non Productive"
End Select 'Make sure the no of Case's is the same as max S
Set Sht = Worksheets(ShtName)
For lngRow = 4 To 49   'Rows 4 thru 49
With Sht
For r = 4 To 49 Step 1 ' HIDES ROWS WITH NOTHING IN "B"
    If Range("B" & r).Value = "" Then
        Rows(r).EntireRow.Hidden = True
    End If
changecount = changecount + 1
pctDone = changecount / cnt
       With UserForm1
            .FrameProgress.Caption = Format(pctDone, "0%")
            .LabelProgress.Width = pctDone * (.FrameProgress.Width - 10)
        End With
        'The DoEvents statement is responsible for the form updating
        DoEvents
Next r
End With
Next lngRow
Next S
   Unload UserForm1
Set Sht = Nothing

Application.ScreenUpdating = True
MsgBox "Phase sheets have been updated!"
End Sub

some parts of this code are used for updating a userform progress bar.

 

[plost33]

no ideas?