VBA code to hide a row does not work

[Legacy 330376]

Hi All,

I have a VBA code which does the following-
* Hides a row if the values of column J and O are less than 12

It should look at row 3 and beyond.

The VBA code is below, any ideas why it is not working?
It does not come up with an error message or anything.
The cells which it looks for, are indexed formulas so I am not sure if it is because of this.
I would like to use the code in an automatic state so probably use the private sub worksheet change function.

Sub Change()
Dim i As Long, lastRow As Long
Set wsDATA = ActiveSheet

With wsDATA
For i = 2 To lastRow
If (.Cells(i, "J") < "12" And .Cells(i, "O") < "12") Then
   .Range("A" & i).EntireRow.Hidden = True
   End If
   Next i
   End With
End Sub

 

[diddi]

try this

Sub Change()
    Dim i As Long, lastRow As Long
    Set wsDATA = ActiveSheet

    With wsDATA
        For i = 3 To lastRow
            If .Cells(i, "J") < 12 And .Cells(i, "O") < 12 Then
                .Range("A" & i).EntireRow.Hidden = True
            End If
        Next i
    End With
End Sub

 

[Legacy 330376]

Hi Diddi,
Still does not work.
Might need to rejig the code, what do you think?

 

[shg]

The variable lastrow is not initialized, so it's 0, so the code inside the loop never executes.

 

[dmt32]

As already mentioned, your LastRow variable has not been initialized.

See if this helps:

Sub Change()
    Dim i As Long, lastRow As Long
    Set wsDATA = ActiveSheet
    
    With wsDATA
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastRow
            .Cells(i, 1).EntireRow.Hidden = .Cells(i, "J").Value < 12 And .Cells(i, "O").Value < 12
        Next i
    End With
End Sub

Dave

 

[Legacy 330376]

As already mentioned, your LastRow variable has not been initialized.

See if this helps:

Sub Change()
    Dim i As Long, lastRow As Long
    Set wsDATA = ActiveSheet
    
    With wsDATA
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To lastRow
            .Cells(i, 1).EntireRow.Hidden = .Cells(i, "J").Value < 12 And .Cells(i, "O").Value < 12
        Next i
    End With
End Sub

Dave

Hi Dave,

It comes up with an error on the following line-

 .Cells(i, 1).EntireRow.Hidden .Cells(i, "J").Value < 12 And .Cells(i, "O").Value < 12

Run-Time Error '1004'
Application-defined or object-defined error.

 

[dmt32]

As published, code should work ok but note that line you have highlighted with problem has the equals ("=") sign missing

.Cells(i, 1).EntireRow.Hidden = .Cells(i, "J").Value < 12 And .Cells(i, "O").Value < 12

Also, ensure sheet is not protected when code run.

Dave

 

[Legacy 330376]

As published, code should work ok but note that line you have highlighted with problem has the equals ("=") sign missing

.Cells(i, 1).EntireRow.Hidden = .Cells(i, "J").Value < 12 And .Cells(i, "O").Value < 12

Also, ensure sheet is not protected when code run.

Dave

Hi Dave,
Thank you very much- it works on a test spreadsheet however when I try it on my actual spreadsheet it does not work. This is because columns J and O are index formulas, is there a way to get round it?