Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with a hide row macro (not using filter)
Can anyone help with this macro ?
I need it to hide any rows which have a value of 0 in cols A to H I DON'T want to use filters. Every time I run it, nothing is hidden Thanks in advance Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 300 If Application.WorksheetFunction.CountA( _ .Cells(rw, 1).Range("A1:H1")) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub |
#2
|
|||
|
|||
Using that method, try it this way
Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 300 If Application.CountA(Range(.Cells(rw, 1), .Cells(rw, 8))) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub -- Don Guillett SalesAid Software wrote in message ups.com... Can anyone help with this macro ? I need it to hide any rows which have a value of 0 in cols A to H I DON'T want to use filters. Every time I run it, nothing is hidden Thanks in advance Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 300 If Application.WorksheetFunction.CountA( _ .Cells(rw, 1).Range("A1:H1")) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub |
#3
|
|||
|
|||
I'll give it a try tomorrow, thanks for the very quick reply
|
#4
|
|||
|
|||
Let us know
-- Don Guillett SalesAid Software wrote in message ups.com... I'll give it a try tomorrow, thanks for the very quick reply |
#5
|
|||
|
|||
One way:
If you really want to hide rows where cols A-H values are zero (not blank): Public Sub Hide_Print_Unhide() Dim rCell As Range Application.ScreenUpdating = False With Sheets("Sheet1") For Each rCell In .Range("A1:A300") rCell.EntireRow.Hidden = Application.CountIf( _ rCell.Resize(1, 8), 0) = 8 Next rCell .PrintOut Preview:=True .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub In article . com, wrote: Can anyone help with this macro ? I need it to hide any rows which have a value of 0 in cols A to H I DON'T want to use filters. Every time I run it, nothing is hidden Thanks in advance Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 300 If Application.WorksheetFunction.CountA( _ .Cells(rw, 1).Range("A1:H1")) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub |
#6
|
|||
|
|||
I think that should do the same thing as the OP's routine. If the OP
isn't seeing any rows hidden, it's likely that he has either formula or non-printing text (e.g, space characters) in the target rows, which COUNTA() will see as non-blank. In article , "Don Guillett" wrote: Using that method, try it this way Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 300 If Application.CountA(Range(.Cells(rw, 1), .Cells(rw, 8))) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub |
#7
|
|||
|
|||
The OP was testing the same range every row.
-- HTH Bob Phillips "JE McGimpsey" wrote in message ... I think that should do the same thing as the OP's routine. If the OP isn't seeing any rows hidden, it's likely that he has either formula or non-printing text (e.g, space characters) in the target rows, which COUNTA() will see as non-blank. In article , "Don Guillett" wrote: Using that method, try it this way Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 300 If Application.CountA(Range(.Cells(rw, 1), .Cells(rw, 8))) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut .Range("A1:A300").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub |
#8
|
|||
|
|||
Hmm... I get the same behavior for each of your routines.
The OP's .Cells(rw, 1).Range("A1:H1") and your Range(.Cells(rw, 1), .Cells(rw, 8)) produce identical range references each time through the loop, right? In article , "Bob Phillips" wrote: The OP was testing the same range every row. |
#10
|
|||
|
|||
Thanks for the info guys,
One more thing .....what if I wanted to hide the row where the contents of col H is 5000 (numeric) or say "total" (or in fact any string). As you may guess I'm still trying to get to grips with these types of macros. Once again thanks in advance |
#11
|
|||
|
|||
another way to hide all rows with a length 0 in col A
Sub hr() For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If Len(Cells(i, 1)) 0 Then Rows(i).Hidden = True Next End Sub -- Don Guillett SalesAid Software wrote in message oups.com... Thanks for the info guys, One more thing .....what if I wanted to hide the row where the contents of col H is 5000 (numeric) or say "total" (or in fact any string). As you may guess I'm still trying to get to grips with these types of macros. Once again thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide rows with macro | Excel Discussion (Misc queries) | |||
Macro for Filter Switches ... Maybe??? | Excel Discussion (Misc queries) | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) | |||
Macro to hide rows with empty cells | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |