VBA - Hide Rows / Columns

[hrayani]

Hi,

Using below code to hide rows based on column A if the value does not equal 1

Sub hide()

  Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "1", , , 0
  
End Sub

Is there a way I can use this code to hide some columns ?

Any help would be appreciated

Regards,

Humayun

 

[Michael M]

Maybe this way...for Row 1

Sub MM1()
Dim c As Long
    For c = 1 To Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
        If Cells(1, c) <> 1 Then Columns(c).EntireColumn.Hidden = True
    Next c
End Sub

 

[hrayani]

Thanks for the reply Michael,

Not working friend-

Run-tiem error 91
Object variable or with block variable not set


Further I would want to hide rows and columns both


For example
Rows From 1 to 10 with values in Columns A which does not equal 1
Columns From A to G with values in Row 14 which dies not equal 1

 

[Trevor_S]

Not exactly the same method, as your code uses autofilter. But this code should check row to and hide columns where the value is not 1:

Sub ColHide()
For n = 1 to Columns.Count
If Cells(2, n).Value = 1 Then
Cells(2, n).EntireColumn.Hidden = False
Else
Cells(2, n).EntireColumn.Hidden =True
End If
Next
End Sub

 

[Trevor_S]

I submitted my post before seeing your previous reply.
Are you saying that you want to hide columns where no rows contain the value 1, and hide rows where no columns contain the value 1?

 

[hrayani]

Hi Trevor,

Thanks for the reply

As mentioned in Post # 3

Hide Rows From 1 to 10 If values in Columns A does not equal 1
Hide Columns From A to G If values in Row 14 does not equal 1


Also if we can set the range in the code for Rows 1 to 10 & for Columns A to G
In this way the code will look into the defined range instead of looking into the entire sheet - I reckon

 

[Fluff]

How about

Sub hrayani()
Dim Cl As Range
Range("A1:A10").AutoFilter 1, "1", , , 0
For Each Cl In Range("A14:G14")
   Cl.EntireColumn.Hidden = Cl.Value <> 1
Next Cl
End Sub

 

[Trevor_S]

Or try this to do rows and columns:

Sub HideMacro()
 
Dim myRowRange As Range
Dim myColRange As Range
Set myRowRange = Range("A1:A10")
Set myColRange = Range("A14:G14")
 
For Each cell In myRowRange
If cell.Value = 1 Then
cell.EntireRow.Hidden = False
Else
cell.EntireRow.Hidden = True
End If
Next
 
For Each cell In myColRange
If cell.Value = 1 Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
Next
 
End Sub

The two Set... rows near the top define the ranges that are checked for 1's.

 

[hrayani]

Hi,

Thanks to everybody for the solution. All working fine.

But I am gonna go with Fluff"s code
Amended as per my actual ranges & using it in the worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cl As Range
Range("A1:A142").AutoFilter 1, "TRUE", , , 0
For Each Cl In Range("E143:AL143")
   Cl.EntireColumn.Hidden = Cl.Value <> True
Next Cl


End Sub

This one is the fastest & shortest Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback