problem with VBA

[CarpKing]

I am developing an excel solution and so need a little help with VBA. I have developed 2 macros that run on the spreadsheet "Sheet1" and have linked them to buttons on the "Menu" Sheet. The macros run with no errors on the "sheet1" sheet however have no effect if run from the "menu" sheet. So I think there is some problem with my referencing, perhaps in the "col" or "rng" definition. I enclose the two modules below, any help would be much appreciated.

Sub hide_specified_rows()
Dim col As Integer
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
'specify column to check
col = ThisWorkbook.Worksheets("Sheet1").Range("AF3").Value

Rows.Hidden = False
Set rng = Range(Cells(1, col), Cells(Rows.Count, col).End(xlUp))
For i = rng.Rows(rng.Rows.Count).Row To 2 Step -1
  If Cells(i, col).Value = 0 Then
    Cells(i, col).EntireRow.Hidden = True
  End If
Next
End Sub

Sub unhide_all_rows()
Dim col As Integer
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
'specify column to check
col = 1
Rows.Hidden = False
Set rng = Range(Cells(1, col), Cells(Rows.Count, col).End(xlUp))
For i = rng.Rows(rng.Rows.Count).Row To 2 Step -1
  If Cells(i, col).Value = "all" Then
    Cells(i, col).EntireRow.Hidden = False
  End If
Next
End Sub

[shasur]

Hi

Try explicitly referencing the Ranges in

Range(Cells(1, col), Cells(Rows.Count, col).End(xlUp))
Cells(i, col).Value

with the sheet names, i.e., Sheets("Menu).Cells(i, col).Value etc

Cheers
Shasur

[CarpKing]

Hi Shasur,

Thanks for the suggestion, i had actually started out with the code with that kind of reference

Sub hide_specified_rows()
Dim col As Integer
Dim rng As Range
Dim rng1 As Range
Dim i As Long
Application.ScreenUpdating = False
'specify column to check
col = ThisWorkbook.Worksheets("Sheet1").Range("AF3").Value

Rows.Hidden = False
Set rng = ThisWorkbook.Worksheets("Sheet1").Range(Cells(1, col), Cells(Rows.Count, col).End(xlUp))
For i = rng.Rows(rng.Rows.Count).Row To 2 Step -1
  If Cells(i, col).Value = 0 Then
    Cells(i, col).EntireRow.Hidden = True
  End If
Next
End Sub

However, with the explicit referencing above the macro retuns a run-time error '1004', application-defined or object-defined error when run from the menu sheet. Any ideas ?