Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows that are empty without autofilter between data
Hey,
I need a VB script that will hide rows in an invoice that are not full of data. If I only have 1 item, but my invoice has 90 lines of possible data, I'd like for it to hide the 89 remaining rows before the subtotal so it can be neat and fit nicely on 1 page. If you can help, thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows that are empty without autofilter between data
Hi Tony
This example will loop through row 1:30 in "Sheet1" If every cell in column A:G is empty it will hide that row. After the loop it print the sheet and then unhide the rows. You can also use this with non contiguous ranges Range("B1,D1:G1") If the cells in column B and D:G are empty it will hide that row. Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 30 If Application.WorksheetFunction.CountA( _ .Cells(rw, 1).Range("A1:G1")) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut ' for testing use .PrintPreview .Range("A1:A30").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub -- Regards Ron De Bruin http://www.rondebruin.nl "Tony" wrote in message ... Hey, I need a VB script that will hide rows in an invoice that are not full of data. If I only have 1 item, but my invoice has 90 lines of possible data, I'd like for it to hide the 89 remaining rows before the subtotal so it can be neat and fit nicely on 1 page. If you can help, thanks in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide rows that are empty without autofilter between data
Thank you very much this was helpful and worked
"Ron de Bruin" wrote: Hi Tony This example will loop through row 1:30 in "Sheet1" If every cell in column A:G is empty it will hide that row. After the loop it print the sheet and then unhide the rows. You can also use this with non contiguous ranges Range("B1,D1:G1") If the cells in column B and D:G are empty it will hide that row. Sub Hide_Print_Unhide() Dim rw As Long Application.ScreenUpdating = False With Sheets("Sheet1") For rw = 1 To 30 If Application.WorksheetFunction.CountA( _ .Cells(rw, 1).Range("A1:G1")) = 0 Then _ .Rows(rw).Hidden = True Next rw .PrintOut ' for testing use .PrintPreview .Range("A1:A30").EntireRow.Hidden = False End With Application.ScreenUpdating = True End Sub -- Regards Ron De Bruin http://www.rondebruin.nl "Tony" wrote in message ... Hey, I need a VB script that will hide rows in an invoice that are not full of data. If I only have 1 item, but my invoice has 90 lines of possible data, I'd like for it to hide the 89 remaining rows before the subtotal so it can be neat and fit nicely on 1 page. If you can help, thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Rows based on value, but not using autofilter | Excel Worksheet Functions | |||
hide empty rows on open | Excel Discussion (Misc queries) | |||
faster way to hide rows w/o using autofilter? | Excel Programming | |||
Hide Empty Rows When Printing | Excel Worksheet Functions | |||
Format to hide empty rows | Excel Discussion (Misc queries) |