karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
110
Office Version
  1. 365
Platform
  1. Windows
hi All,
I have a problem with the workbook I work on. In one Tab (Tab1) with command buttons (Names A,B,C and D). The second tab is a table with a data for A, B, C and D. I want to hide all rows with 0 in column C in Tab2 if I press command button 1

I have a code which works in the development mode but doesn't work whe nI actually use Command buttons:

Dim r As Long
For r = 3 To 35
If Cells(r, 3) = 0 Then
Rows(r).EntireRow.Hidden = True
Else
Rows(r).EntireRow.Hidden = False
End If
Next r

Any help will be much appreciated.
 
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
 
Upvote 0

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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 :(
 
Upvote 0
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
 
Upvote 0
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 :(
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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"?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top