Quicker VBA needed

[Dimbers]

Evening,

I have a VBA code that works as it should but is a little clunky to use, as in its quite slow.

The code looks in column A and if it finds "Live" it will hide all of those rows. I have the same code running to look for "Complete" and will then hide those rows.

It would be great to have a code that runs quicker if anyone can offer some help?

Below is the code I'm using

Sub HideRowsLive()

Application.ScreenUpdating = False

 Dim cell As Range

  For Each cell In ActiveWorkbook.ActiveSheet.Columns("A").Cells

      If cell.Value = "Complete" Then
      
       cell.EntireRow.Hidden = False
       
       End If
  
  Next cell
  
  For Each cell In ActiveWorkbook.ActiveSheet.Columns("A").Cells

       
       If cell.Value = "Live" Then
      
       cell.EntireRow.Hidden = False
       
       End If

  Next cell
  

  For Each cell In ActiveWorkbook.ActiveSheet.Columns("A").Cells

      If cell.Value = "Live" Then

      cell.EntireRow.Hidden = True
      

    End If

  Next cell

 

[Joe4]

Your last two blocks seem to contradict each other (not sure if you want to hide or unhide "Live" cells).

See if this does what you want:

Sub HideRowsLive()

Application.ScreenUpdating = False

 Dim cell As Range
 Dim lr As Long
 
 'Find last row in column A with data
 lr = Cells(Rows.Count, "A").End(xlUp).Row

  For Each cell In Range("A1:A" & lr)

      If (cell.Value = "Complete") Or (cell.Value = "Live") Then
          cell.EntireRow.Hidden = False
      End If
 
  Next cell
 
Application.ScreenUpdating = True

End Sub

It should be a bit faster as you only need one loop, and you do not need to loop through every single row on the sheet, just down to the last cell in column A with a value.

 

[Dimbers]

Thanks Joe,

Thats much faster

I should have mentioned, but didn't think, that the first part of the code is to show all live and completed rows as they may have already been filtered by one of the commands already.

The second part is to hide "Complete" and show only Live

 

[Alex Blakenburg]

The second part is to hide "Complete" and show only Live

That gets a little confusing. I assume you have other categories in addition to Complete and Live.
Can we Hide All and then Unhide Live or do we need to keep track of the status of other hidden rows.

For example if the starting point is per the below, what do you want the end result to be.

20230923 VBA Hide Unhide Dimbers.xlsm
	A	B
5	Status	Initial setting
6	Complete	Hidden
7	Started	Visible
8	Live	Hidden
9	Under Review	Hidden
Notes

Sample XL2BB

20230923 VBA Hide Unhide Dimbers.xlsm
	A
1	Status
2	Complete
3	Started
4	Live
5	Live
6	Under Review
7	Live
8	Complete
9	Started
10	Live
11	Under Review
12	Complete
13	Started
14	Live
15	Under Review
16	Complete
17	Started
18	Live
Data

 

[kevin9999]

In addition to @Alex Blakenburg 's questions in post #4, can I ask if there are rows currently hidden - are they hidden manually, by some existing code, or by using Autofilter?

 

[dmt32]

like others here, little confused by your post but if you only want to HIDE all rows that have "Complete" in the range then rather than doing this row by row, a faster method would be to create a Union of ranges & then hide them in one go

Give following a try & see if does what you want

Sub HideComplete()
    Dim c As Range, HideRange As Range, DataRange As Range
    
    With Worksheets("Sheet1")
        .Unprotect
        'unhide all rows
        .Cells.Rows.Hidden = False
        'set data range
        Set DataRange = .Cells(1, 1).Resize(.Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    For Each c In DataRange.Cells
        'create union
        If UCase(c.Value) = "COMPLETE" Then
            If HideRange Is Nothing Then
                Set HideRange = c
            Else
                Set HideRange = Union(HideRange, c)
            End If
        End If
    Next c
    'hide rows in one go
    If Not HideRange Is Nothing Then HideRange.EntireRow.Hidden = True
End Sub

Change the sheet name as required

Dave

 

[kevin9999]

Another option...

Option Explicit
Option Compare Text
Sub Test_Hide_Complete()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")       '<-- *** Change to actual sheet name ***
    
    If ws.AutoFilterMode Then ws.AutoFilter.ShowAllData
    ws.UsedRange.EntireRow.Hidden = False
    With ws.Cells(1, 1)
        .EntireRow.Insert
        .Offset(-1).Value = "Temp"
    End With
    
    Dim rCrit As Range
    With ws.Range(ws.Cells(1, 1), ws.Cells(ws.Cells(Rows.Count, 1).End(xlUp).Row, _
    ws.Cells(1, Columns.Count).End(xlToLeft).Column))
        Set rCrit = .Offset(, .Columns.Count).Resize(2, 1)
        rCrit.Cells(2).Formula = "=COUNTIF(A2,""*Complete*"")=0"
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
    End With
    rCrit.Cells(2).ClearContents
    ws.Cells(1, 1).EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

 

[Dimbers]

Thanks for all your replies,

Dave, il give your code a try shortly and get back with how it works.

Il break down the workbook as its probably my explanation thats confusing

I have 2 template sheets that run from 2 macros

* Macro 1 (Add new Client) = Copies Template 1 and pastes into a new sheet
* Macro 2 (Add new Job) = Copies Template 2 from the next available line on the client sheet

You can keep adding new jobs to a client as many times as required, these just keep adding from the next available line

Id like the user to be able to filter the sheet to only show either live work or completed work, with another another macro to Show All (expanding both live and complete)

If the user runs the macro to show Live and then the macro to show Complete without Show All in between, then both Live and Complete are hidden.

The Macro needs to make sure both Live and Complete are expanded before it then hides the selection

I've added screenshots of both Templates, you can see in Column A it shows Live or Complete on each row that i want hidden when the macro is run.

With Template 1 being the Add new Client it needs to keep rows 2 to 5 visible at all times, this has the client info at the top as well as the button controls for the sheet. Row 1 is hidden as there are some codes that it feeds the data from in there.

With Template 2 being the Add new Job, all rows can be hidden when the Macro is run. However, row 1 is always hidden to hide the data stored in there.


Template 1 (Add New Client)

Template 2 (Add new Job)

 

[Dimbers]

Sorry i added the same template

Below is Template 2, Add new Job

 

[dmt32]

Id like the user to be able to filter the sheet to only show either live work or completed work, with another another macro to Show All (expanding both live and complete)

You should only need one code to perform all three tasks

See if this update to suggested code will do what you want

Place BOTH codes in a STANDARD module

Enum HideLiveComplete
    HideLive = 1
    HideComplete
    ShowAll
End Enum

Sub HideRows(ByVal UserAction As HideLiveComplete)
    Dim c               As Range, HideRange As Range, DataRange As Range
    Dim HideWhat        As String
    
    With Worksheets("Sheet1")
        .Unprotect
        'unhide all rows
        .Cells.Rows.Hidden = False
        If UserAction = ShowAll Then Exit Sub
        'set data range
        Set DataRange = .Cells(1, 1).Resize(.Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    HideWhat = Choose(UserAction, "LIVE", "COMPLETE")
    
    For Each c In DataRange.Cells
        'create union
        If UCase(c.Value) = HideWhat Then
            If HideRange Is Nothing Then
                Set HideRange = c
            Else
                Set HideRange = Union(HideRange, c)
            End If
        End If
    Next c
    'hide rows in one go
    If Not HideRange Is Nothing Then HideRange.EntireRow.Hidden = True
End Sub

The updated code has the parameter UserAction which allows you to to tell it what action to perform

when calling the code, you will see the IntelliSense display the options you can select

example

Sub Dimbers()
    HideRows ShowAll
End Sub

Hope Helpful

Dave