Hide Blanks Rows

[LBTravis]

Hi there,
I have a Macro that is hiding both blank rows and columns in one tab. I want to modify this to only hide blank rows in a different tab. Can anyone help me figure out which parts of this macro I need to delete?
Thanks!

Sub Hide()


Application.ScreenUpdating = False
'The above doesn't show all the steps. It is cosmetic.

'unhide rows and columns first. If new rows or columns are added we may need to extend ranges
Sheets("6 - Analysis CF").Select

Rows("16:250").Select
Selection.EntireRow.Hidden = False
Columns("G:ZZ").Select
Selection.EntireColumn.Hidden = False


Sheets("6 - Analysis CF").Select

'Rows hiding

For i = 26 To 250

CellContents = Range("A" & i).Value
'range A for column i variable for all the rows.

If CellContents = "NoValue" Then

Rows(i & ":" & i).Select
'It evaluates one row each time.
Selection.EntireRow.Hidden = True
Range("A" & i).Select

End If

Next

'Columns hiding
Range("H1").Select
'This needs to be the first cell that has our Value/NoValue formula
DoneCounting = False
' The donecounting is Boolean i.e. True/False.
Do Until DoneCounting = True

If ActiveCell.Value = "NoValue" Then

Selection.EntireColumn.Hidden = True

ElseIf ActiveCell.Value = "" Then
'This ="" means blank. This will stop our Loop i.e. our Macro

DoneCounting = True
Exit Do

End If

ActiveCell.Offset(0, 1).Activate
' This means that if E1 is value go to the next column and look for Value/NoValue condition
Loop

Sheets("6 - Analysis CF").Select


Range("H22").Select
'This puts the cursor in cell H22


MsgBox ("Done Hiding")


End Sub




Sub UnHide()


Application.ScreenUpdating = False


'unhide rows and columns. If new rows or columns are added we may need to extend ranges
Rows("16:250").Select
Selection.EntireRow.Hidden = False
Columns("G:ZZ").Select
Selection.EntireColumn.Hidden = False

Sheets("6 - Analysis CF").Select

Range("A1").Select


MsgBox ("Done Unhiding")


End Sub

 

[Fishboy]

Hi there,
I have a Macro that is hiding both blank rows and columns in one tab. I want to modify this to only hide blank rows in a different tab. Can anyone help me figure out which parts of this macro I need to delete?
Thanks!

Sub Hide()


    Application.ScreenUpdating = False
    'The above doesn't show all the steps. It is cosmetic.

'unhide rows and columns first. If new rows or columns are added we may need to extend ranges
Sheets("6 - Analysis CF").Select

    Rows("16:250").Select
    Selection.EntireRow.Hidden = False
    Columns("G:ZZ").Select
    Selection.EntireColumn.Hidden = False


Sheets("6 - Analysis CF").Select

    'Rows hiding

     For i = 26 To 250

        CellContents = Range("A" & i).Value
    'range A for column i variable for all the rows.

            If CellContents = "NoValue" Then

                Rows(i & ":" & i).Select
                'It evaluates one row each time.
                Selection.EntireRow.Hidden = True
                Range("A" & i).Select

            End If

    Next

'Columns hiding
    Range("H1").Select
    'This needs to be the first cell that has our Value/NoValue formula
    DoneCounting = False
    ' The donecounting is Boolean i.e. True/False.
    Do Until DoneCounting = True

        If ActiveCell.Value = "NoValue" Then

            Selection.EntireColumn.Hidden = True

        ElseIf ActiveCell.Value = "" Then
        'This ="" means blank. This will stop our Loop i.e. our Macro

            DoneCounting = True
            Exit Do

        End If

        ActiveCell.Offset(0, 1).Activate
' This means that if E1 is value go to the next column and look for Value/NoValue condition
    Loop

Sheets("6 - Analysis CF").Select


    Range("H22").Select
'This puts the cursor in cell H22


    MsgBox ("Done Hiding")


End Sub




Sub UnHide()


    Application.ScreenUpdating = False


'unhide rows and columns. If new rows or columns are added we may need to extend ranges
    Rows("16:250").Select
    Selection.EntireRow.Hidden = False
    Columns("G:ZZ").Select
    Selection.EntireColumn.Hidden = False

Sheets("6 - Analysis CF").Select

    Range("A1").Select


    MsgBox ("Done Unhiding")


End Sub

Hi LBTravis, welcome to the boards.

If it is purely the tab that needs to be changed and the rest of the code needs to remain as it is, you just need to replace the bold part in red from the following line...

Sheets("6 - Analysis CF").Select

...with whatever the name of the desired sheet is. It seems that line occurs more than once throughout your code so be sure to replace each and every instance of it with the correct sheet name.

I hope this helps.

 

[Jake Blackmore]

Hi,

I just wanted to experiment with your code a little with a view to refine it. The logic being you can call these macro on a sheet activation event or a button on the sheet if you like:

Option Explicit

Public Sub Hide()
    Dim blnScreen           As Boolean
    Dim blnDoneCalculating  As Boolean
    Dim lngItem             As Long

    With Application
        blnScreen = .ScreenUpdating
        .ScreenUpdating = False
    End With

    With ActiveSheet
        .Columns("G:ZZ").EntireColumn.Hidden = False
        For lngItem = 26 To 250
            If .Range("A" & lngItem).Value = "NoValue" Then
                .Rows(lngItem & ":" & lngItem).EntireRow.Hidden = True
            Else
                .Rows(lngItem & ":" & lngItem).EntireRow.Hidden = False
            End If
        Next lngItem
        blnDoneCounting = False
        For lngItem = 8 To .UsedRange.Columns.Count
            If .Cells(1, lngItem).Value = "NoValue" Then .Cells(1, lngItem).EntireColumn.Hidden = True
        Next lngItem
        Call Application.Goto(reference:=.Range("H22"), scroll:=True)
    End With

    Application.ScreenUpdating = blnScreen
    Call MsgBox(prompt:="Done Hiding", Buttons:=vbOKOnly)

End Sub

Public Sub UnHide()
    Dim blnScreen           As Boolean
    
    With Application
        blnScreen = .ScreenUpdating
        .ScreenUpdating = False
    End With

    With ActiveSheet
        .Rows("16:250").EntireRow.Hidden = False
        .Columns("G:ZZ").EntireColumn.Hidden = False
    End With

    Call Application.Goto(reference:=.Range("a1"), scroll:=True)

    Application.ScreenUpdating = True

    Call MsgBox(promt:="Done Unhiding", Buttons:=vbOKOnly)
End Sub

Let me know if it all works ok.

 

[LBTravis]

I only want to hide blank rows on the new tab. So I need to delete any code that is hiding columns.
Thanks for your help!

Hi LBTravis, welcome to the boards.

If it is purely the tab that needs to be changed and the rest of the code needs to remain as it is, you just need to replace the bold part in red from the following line...

Sheets("6 - Analysis CF").Select

...with whatever the name of the desired sheet is. It seems that line occurs more than once throughout your code so be sure to replace each and every instance of it with the correct sheet name.

I hope this helps.

 

[LBTravis]

I do have a button that I want to assign it to. But need to delete anything in the code that hides columns. I only want to hide rows.