Use formula to define "Const" value when hidding rows

[alecwarner]

Hi

I use the code below in several places to "hide" rows that have no data. I'm trying to speed up the code by reducing the range of rows.

Is it possible to define the "end_row2" constant by a formula instead of a fixed number 118?

I tried already:

Const end_row2 As Long = Sheet16.Range("$L$78").Value

But this just alarms out????????

Any help would be appreciated.

Const start_row2 As Long = 79
Const check_col2 As Long = 5
Const end_row2   As Long = 118
    
Dim r2           As Range
Dim x2           As Long
    
Application.ScreenUpdating = False
        
With Sheet16
'     With .Cells(start_row2, 1).Resize(end_row2 - start_row2 + 1).EntireRow
'         .AutoFit
'         .Hidden = False
'     End With
For x2 = start_row2 To end_row2
If Len(.Cells(x2, check_col2).Value) = 0 Or .Cells(x2, check_col2).Value = 0 Then
If not r2 Is Nothing Then
Set r2 = Union(r2, .Cells(x2, check_col2))
Else
Set r2 = .Cells(x2, check_col2)
End If
End If
Next x2
If Not r2 Is Nothing Then
r2.EntireRow.Hidden = True
Set r2 = Nothing
End If
End With

 

[RoryA]

A constant is, by definition, constant. You need a variable - eg:

Dim end_row2 As Long
end_row2 = Sheet16.Range("$L$78").Value

Also, please use code tags when posting code.

 

[alecwarner]

Thanks, obvious when you know how!