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. |
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. |
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. |
All times are GMT +1. The time now is 06:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com