VBA running slow any suggestions??

[dimples_21188]

Please could someone help me with the code below, it runs very slow, is there a way to speed it up? Thank you

Sub HideUnusedRowsInventory()
Dim LR As Long, i As Long
'LR = Range("b" & Rows.Count).End(xlUp).Row
LR = 1468
For i = 129 To 1468
Rows(i).Hidden = Range("b" & i).Value = 0
Next i
Application.ScreenUpdating = True
End Sub

 

[steve the fish]

See if this quicker

Sub HideUnusedRowsInventory()

Dim FR As Long, LR As Long, arrRng As Range, arr, rng As Range, i As Long

Application.ScreenUpdating = False

'LR = Range("b" & Rows.Count).End(xlUp).Row
FR = 129
LR = 1468

Set arrRng = Range(Cells(FR, 2), Cells(LR, 2))
arrRng.EntireRow.Hidden = False
arr = arrRng
For i = LBound(arr) To UBound(arr)
    If Len(arr(i, 1)) > 0 And arr(i, 1) = 0 Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + FR - 1, 2))
        Else
            Set rng = Cells(i + FR - 1, 2)
        End If
    End If
Next
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True
    
End Sub

 

[dimples_21188]

See if this quicker

Sub HideUnusedRowsInventory()

Dim FR As Long, LR As Long, arrRng As Range, arr, rng As Range, i As Long

Application.ScreenUpdating = False

'LR = Range("b" & Rows.Count).End(xlUp).Row
FR = 129
LR = 1468

Set arrRng = Range(Cells(FR, 2), Cells(LR, 2))
arrRng.EntireRow.Hidden = False
arr = arrRng
For i = LBound(arr) To UBound(arr)
    If Len(arr(i, 1)) > 0 And arr(i, 1) = 0 Then
        If Not rng Is Nothing Then
            Set rng = Union(rng, Cells(i + FR - 1, 2))
        Else
            Set rng = Cells(i + FR - 1, 2)
        End If
    End If
Next
If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True
   
End Sub

You sir are a genius thank you

 

[dimples_21188]

You sir are a genius thank you

Is it possible to get it run on the sheet rather than a button or would make the spreadsheet run too slow?

 

[steve the fish]

What do you mean by run on the sheet?

 

[dimples_21188]

What do you mean by run on the sheet?

I would ideally like the code you wrote to run on a worksheet automatically. I already have a Private Sub Worksheet_Change(ByVal Target As Range) running on the worksheet, I didn't know if I could simply add the code underneath? however would this then make my spreadsheet run slow because it would always be running?

 

[steve the fish]

This would do it if you are just changing one cell at a time.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim FR As Long, LR As Long, arrRng As Range, arr, rng As Range, i As Long

Application.ScreenUpdating = False

'LR = Range("b" & Rows.Count).End(xlUp).Row
FR = 129
LR = 1468

Set arrRng = Range(Cells(FR, 2), Cells(LR, 2))

If Target.Cells.Count = 1 Then
    If Not Intersect(Target, arrRng) Is Nothing Then
        arrRng.EntireRow.Hidden = False
        arr = arrRng
        For i = LBound(arr) To UBound(arr)
            If Len(arr(i, 1)) > 0 And arr(i, 1) = 0 Then
                If Not rng Is Nothing Then
                    Set rng = Union(rng, Cells(i + FR - 1, 2))
                Else
                    Set rng = Cells(i + FR - 1, 2)
                End If
            End If
        Next
        If Not rng Is Nothing Then rng.EntireRow.Hidden = True
    End If
End If

Application.ScreenUpdating = True
    
End Sub

 

[steve the fish]

Thinking about it you had it looping through loads of cells. If you want it to just use a worksheet change then you can make it simpler by just testing 'target'.

 

[dimples_21188]

Thinking about it you had it looping through loads of cells. If you want it to just use a worksheet change then you can make it simpler by just testing 'target'.

ok ill give it a go thank you