Hide/unhide rows

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
106
Office Version
365
Platform
Windows
that's great! It now works but I had to switch off this automatic calc.... Thank you very much for a help. Really useful learning
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
106
Office Version
365
Platform
Windows
hi again :)
I was wondering if you know any tricks/hints/codes which would speed up my macro. I have applied above to a different model and it works perfectly fine other than it's very slow :(
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,974
Office Version
2013
Platform
Windows
Try using

Code:
Sub karolina1406()
Dim Rw As Long
Application.ScreenUpdating = False
   With Sheets("Sheet2")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
Application.ScreenUpdating = True
End Sub
 

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
106
Office Version
365
Platform
Windows
Try using

Code:
Sub karolina1406()
Dim Rw As Long
Application.ScreenUpdating = False
   With Sheets("Sheet2")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
Application.ScreenUpdating = True
End Sub
Already got this and its still slow :( The only thing what speeds it up at the moment is if I switch off automatic calc however that then means that the command button needs to be pressed twice to work :(
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,974
Office Version
2013
Platform
Windows
You don't have any worksheet_events running as well, do you ??
If you turn autocalc off and back on at the end of the code shouldn't that then run correctly ??
Code:
Sub karolina1406()
Dim Rw As Long
  with Application
   .ScreenUpdating = False
   .calculation=xlmanual
  end with
   With Sheets("Sheet2")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
  with Application
    .ScreenUpdating = true
    .calculation=xlautomatic
  end with
End Sub
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
You could use an array then you dont need to be hiding/unhiding rows 30 odd times but all at once. Should be quicker.

Code:
Dim i As Long, arr, rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet2")
    .Rows("3:35").Hidden = False
    arr = .Range(.Cells(3, 3), .Cells(35, 3))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + 2, 1))
            Else
                Set rng = .Cells(i + 2, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True
 

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
106
Office Version
365
Platform
Windows
You could use an array then you dont need to be hiding/unhiding rows 30 odd times but all at once. Should be quicker.

Code:
Dim i As Long, arr, rng As Range

Application.ScreenUpdating = False

With Sheets("Sheet2")
    .Rows("3:35").Hidden = False
    arr = .Range(.Cells(3, 3), .Cells(35, 3))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + 2, 1))
            Else
                Set rng = .Cells(i + 2, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True
sorry for being a Blonde but which part of this code does hide all the rows if in column C I have 0 "zero"?
 

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
106
Office Version
365
Platform
Windows
great, that's fine... I tried to apply the same code for another tab (within this same command button) which is called "Pogramme_Plan" and the filtering (0,1) is in column A from row 9. I applied below:

With Sheets("Programme_Plan")
.Rows("9:500").Hidden = False
.Range("a7").Value = Sheets("Summary").Range("d7")
arr = .Range(.Cells(3, 3), .Cells(500, 1))
For i = LBound(arr) To UBound(arr)
If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
If Not rng Is Nothing Then
Set rng = Union(rng, .Cells(i + 2, 1))
Else
Set rng = .Cells(i + 2, 1)
End If
End If
Next
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True


but I am getting an error at the

Set rng = Union(rng, .Cells(i + 2, 1)) line... any idea why?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,893
Messages
5,489,561
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top