Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Hi all,
I´m trying to "clean" the worksheet from unused rows before printing and then get a print out that only shows whats necessary. The "cleaned area is different every time, depending wether there are numbers i them or not. I´m using the code below, (thanks NOSPAM), but I get very long "answertime" from this code. Is there anyway to modify it t work from D5:D206? I think it would go faster if the area is specifie to D5:D206, than to the bottom row, or.......? I assume I have to modify it more than just change the area B:B becouse that doesnt work. Thanks in advance! //Thomas Sub CleanUporg() Dim CurrentRow As Long Dim UsedRows As Range On Error GoTo Abort Set UsedRows = ActiveSheet.UsedRange.Rows For CurrentRow = UsedRows.Rows.Count To 1 Step -1 I Application.WorksheetFunction.Sum(UsedRows.Rows(Cu rrentRow).Columns("B:B") = 0 Then UsedRows.Rows(CurrentRow).EntireRow.Hidden = True Else: UsedRows.Rows(CurrentRow).EntireRow.Hidden = False End If Next CurrentRow ' If only hiding for printing purposes, use the next two lines to prin or 'preview then restore the worksheet 'ActiveWindow.SelectedSheets.PrintPreview 'ActiveSheet.Rows.EntireRow.Hidden = False Abort: End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
I'm confused. What exactly has to be true in order to hide a row?
-- Jim Rech Excel MVP "Jonsson " wrote in message ... | Hi all, | | I´m trying to "clean" the worksheet from unused rows before printing, | and then get a print out that only shows whats necessary. The "cleaned" | area is different every time, depending wether there are numbers in | them or not. I´m using the code below, (thanks NOSPAM), but I get a | very long "answertime" from this code. Is there anyway to modify it to | work from D5:D206? I think it would go faster if the area is specified | to D5:D206, than to the bottom row, or.......? | | I assume I have to modify it more than just change the area B:B, | becouse that doesnt work. | | Thanks in advance! | | //Thomas | | | Sub CleanUporg() | Dim CurrentRow As Long | Dim UsedRows As Range | On Error GoTo Abort | Set UsedRows = ActiveSheet.UsedRange.Rows | For CurrentRow = UsedRows.Rows.Count To 1 Step -1 | If | Application.WorksheetFunction.Sum(UsedRows.Rows(Cu rrentRow).Columns("B:B")) | = 0 Then | UsedRows.Rows(CurrentRow).EntireRow.Hidden = True | Else: UsedRows.Rows(CurrentRow).EntireRow.Hidden = False | End If | Next CurrentRow | ' If only hiding for printing purposes, use the next two lines to print | or | 'preview then restore the worksheet | 'ActiveWindow.SelectedSheets.PrintPreview | 'ActiveSheet.Rows.EntireRow.Hidden = False | Abort: | End Sub | | | --- | Message posted from http://www.ExcelForum.com/ | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Hi,
I have column C, it´s always contains values and formulas. From C5:C206 But, when column D, which contains formulas with lookupfunction don generate a value, depending on whats in col C, I want that specifi row/rows to be hidden. Hope you understand what I mean! Thanks in advance!! //Thoma -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Hi,
hide/show rows depending on value in column D: Sub hide_rows() For i = 5 To 206 'rows If Range("D" & i).Value = 0 Then Rows(i).EntireRow.Hidden = True Else Rows(i).EntireRow.Hidden = False End If Next i End Sub or shorter version Sub hide_rows2() For i = 5 To 206 Rows(i).EntireRow.Hidden = Range("D" & i).Value = 0 Next i End Sub Jare -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Hi Jarek
Thanks for your help! But it seems to be an error in your code. I get "undifinied variabel as error messege. I think there is "For i" that is not definie correctly. Can you or somebody else help me with this, please? I think we are on the right track here. //Thoma -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Hi,
add variable declaration at the first line: Sub hide_rows2() Dim i As Long For i = 5 To 206 Rows(i).EntireRow.Hidden = Range("D" & i).Value = 0 Next i End Sub Jare -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Hi, Jared! And all you out there
The code you, Jared, gave me works nice! Thanks! No, is it possible to do the same procedure but let the macro chec several different columns, lets say 7 of them? I have tried to write for instance"E", "G", "T" columns, instead o just "E", but that dont work, of course. Any idea´s? //Thoma -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
I'd like to propose another approach that you may or may not like...
Off to the right side of your data you could have a column of formulas that determine whether the row is to be hidden. Let's say that column Z is out of sight, so in cell Z5 you enter this formula: =IF(SUM(D5,F5,G5)=0,TRUE,1) You can include all the cells you want to check on row 5. Then copy this formula from Z5 all the way to Z206. If the formula returns True the row is be hidden, if it returns 1 it is not. Then the macro can be as simple as this: Application.ScreenUpdating = False On Error Resume Next With Range("Z5:Z206") .Rows.Hidden = False ''not needed if all rows are visible to start with .SpecialCells(xlCellTypeFormulas, xlLogical).Rows.Hidden = True End With If you want you can hide column Z and no one will know it's there. -- Jim Rech Excel MVP "Jonsson " wrote in message ... Hi, Jared! And all you out there The code you, Jared, gave me works nice! Thanks! No, is it possible to do the same procedure but let the macro check several different columns, lets say 7 of them? I have tried to write for instance"E", "G", "T" columns, instead of just "E", but that dont work, of course. Any idea´s? //Thomas --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide and printout area conditions
Thanks Jim and Jarek!
I appreciate your help! Both solutions work great!! //Thoma -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i want to hide the some plot area in a chart | New Users to Excel | |||
Hide a row and set print area | Excel Worksheet Functions | |||
Data label area in printout | Charts and Charting in Excel | |||
no #VALUE! to printout | Excel Worksheet Functions | |||
Pivot table Data area show/hide behavior | Excel Discussion (Misc queries) |