PDA

View Full Version : Unhide bug



S.Limb
07-11-2011, 03:32 AM
Hi
I'm pretty new to VBA and macros so please be gently with me.

I've managed to write a simple print macro that will hide a number of rows based on the condition of cell B25. see below While not being the most elegant of piece of code its worked fine.


Sub print_3_copies()
'
' print_3_copies Macro
'
'
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
ActiveWindow.SmallScroll Down:=36
Rows("77:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("76:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 4 Then
Rows("73:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("72:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 3 Then
Rows("69:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("68:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 2 Then
Rows("65:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("64:82").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 1 Then
Rows("61:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("60:81").Select
Selection.EntireRow.Hidden = False
End If
End Sub


I have then extended the print range and added to the macro applying an auto filter that will hide rows with a value 0 see below.


Sub print_3_copies()
'
' print_3_copies Macro
'
'
Range("A83:E231").Select
Selection.AutoFilter
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
ActiveWindow.SmallScroll Down:=36
Rows("77:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("76:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 4 Then
Rows("73:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("72:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 3 Then
Rows("69:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("68:81").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 2 Then
Rows("65:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("64:82").Select
Selection.EntireRow.Hidden = False
ElseIf Range("b25") = 1 Then
Rows("61:80").Select
Selection.EntireRow.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
Rows("60:81").Select
Selection.EntireRow.Hidden = False
End If
ActiveSheet.Range("$A$83:$E$231").AutoFilter Field:=5
End Sub


While the extended code for the autofilter works fine and the print is Ok, the original code will not UNHIDE the hidden rows.

I know this must be a really simple problem but I can't see what I've done.
Any help would be much appreciated.

Thanks

S.Limb
07-11-2011, 05:53 AM
Hi
just realised Ive posted this thread in the wrong section. This should have been posted under Excel.

Sorry

Simon Lloyd
07-11-2011, 11:00 PM
Thread moved :)

Aflatoon
07-12-2011, 12:35 AM
I should start by removing the selecting and scrolling and see if that changes anything:



Sub print_3_copies()
'
' print_3_copies Macro
'
'
With Range("A83:E231")
.AutoFilter
.AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd
End With
If Range("b25") = 6 Then
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
ElseIf Range("b25") = 5 Then
With Rows("77:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 4 Then
With Rows("73:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 3 Then
With Rows("69:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 2 Then
With Rows("65:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
ElseIf Range("b25") = 1 Then
With Rows("61:80")
.Hidden = True
ExecuteExcel4Macro "PRINT(1,,,3,,,,,,,,2,,,TRUE,,FALSE)"
.Hidden = False
End With
End If
ActiveSheet.Range("$A$83:$E$231").AutoFilter
End Sub

S.Limb
07-12-2011, 01:12 AM
Aflatoon

Thats worked a treat.
I knew it would be somthing stupid I was doing.
Thank you very much for your help.