Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
I have been using the code below to hide empty rows in a worksheet.
Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100 rows to determine whether to hide the entire row. The number of rows has grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'All Rows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
Try this. It ran in less than a second for me. Let me know how it goes.
Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'All Rows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hide empty rows in a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100 rows to determine whether to hide the entire row. The number of rows has grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'All Rows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
On Apr 9, 4:42 pm, OssieMac
wrote: Try this. It ran in less than a second for me. Let me know how it goes. Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'AllRows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hideemptyrowsin a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100rowsto determine whether to hide the entire row. The number ofrowshas grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'AllRows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ OssieMac, Thanks for your quick reply. I tried your code, but its hiding rows that it shouldn't. I have to check every column D thru O (ie. 4 to 15) to see if the value is something other than zero. If the value in each column (D thru O) has a zero value then I can hide the row. Any tweak that might fix your code? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
How about:
Option Explicit Sub Test_Hide_Rows2() Dim iRow As Long Dim myRngToCheck As Range Dim HowManyCols As Long HowManyCols = 12 'D to O is 12 columns With ActiveSheet For iRow = 8 To 359 'All Rows Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols) If Application.CountIf(myRngToCheck, 0) = HowManyCols Then 'all 0! .Rows(iRow).Hidden = True End If Next iRow End With End Sub AJ Master wrote: On Apr 9, 4:42 pm, OssieMac wrote: Try this. It ran in less than a second for me. Let me know how it goes. Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'AllRows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hideemptyrowsin a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100rowsto determine whether to hide the entire row. The number ofrowshas grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'AllRows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ OssieMac, Thanks for your quick reply. I tried your code, but its hiding rows that it shouldn't. I have to check every column D thru O (ie. 4 to 15) to see if the value is something other than zero. If the value in each column (D thru O) has a zero value then I can hide the row. Any tweak that might fix your code? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
On Apr 10, 5:34 am, Dave Peterson wrote:
How about: Option Explicit Sub Test_Hide_Rows2() Dim iRow As Long Dim myRngToCheck As Range Dim HowManyCols As Long HowManyCols = 12 'D to O is 12 columns With ActiveSheet For iRow = 8 To 359 'All Rows Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols) If Application.CountIf(myRngToCheck, 0) = HowManyCols Then 'all 0! .Rows(iRow).Hidden = True End If Next iRow End With End Sub AJ Master wrote: On Apr 9, 4:42 pm, OssieMac wrote: Try this. It ran in less than a second for me. Let me know how it goes. Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'AllRows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hideemptyrowsin a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100rowsto determine whether to hide the entire row. The number ofrowshas grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'AllRows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ OssieMac, Thanks for your quick reply. I tried your code, but its hiding rows that it shouldn't. I have to check every column D thru O (ie. 4 to 15) to see if the value is something other than zero. If the value in each column (D thru O) has a zero value then I can hide the row. Any tweak that might fix your code? -- Dave Peterson Dave, Thanks very much! The new code works great and I'm back to the 2-3 sec execution range. Regards...AJ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
Hello
i have a similar situation. i am using excel to import a crosstab query from access. i have a autofilter on the results in the spreadsheet. i want to be able to hide columns based on whether or not there is data below the title of the column, so if column e is empty from rows 2 to 65536 i want it to hide that column. i would like it to happen automattically after a filter from the autofilter is applied, but a button would do. if i filter a column using the auto filter some columns become "empty". yes there is still stuff there in the empty columns, but they are being filtered leaving gaps in the columns. is there a way to use this code to hide those columns that dont show anything? i tried just "reversing" the code to work on columns, but those columns arent really empty, so i the code doesnt work. here was my attempt: Sub testcolumnhide() Dim iColumn As Long Dim myRngtoCheck As Range Dim HowManyRows As Long HowManyRows = 65533 With ActiveSheet For iColumn = 5 To 250 Set myRngtoCheck = .Cells(4, iColumn).Resize(HowManyRows, 1) If Application.CountIf(myRngtoCheck, 0) = HowManyRows Then .Columns(iColumn).Hidden = True End If Next iColumn End With End Sub thanks for any and all help! "Dave Peterson" wrote: How about: Option Explicit Sub Test_Hide_Rows2() Dim iRow As Long Dim myRngToCheck As Range Dim HowManyCols As Long HowManyCols = 12 'D to O is 12 columns With ActiveSheet For iRow = 8 To 359 'All Rows Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols) If Application.CountIf(myRngToCheck, 0) = HowManyCols Then 'all 0! .Rows(iRow).Hidden = True End If Next iRow End With End Sub AJ Master wrote: On Apr 9, 4:42 pm, OssieMac wrote: Try this. It ran in less than a second for me. Let me know how it goes. Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'AllRows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hideemptyrowsin a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100rowsto determine whether to hide the entire row. The number ofrowshas grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'AllRows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ OssieMac, Thanks for your quick reply. I tried your code, but its hiding rows that it shouldn't. I have to check every column D thru O (ie. 4 to 15) to see if the value is something other than zero. If the value in each column (D thru O) has a zero value then I can hide the row. Any tweak that might fix your code? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
I think you're doing...
Filter the range look at each column in the filtered range if there are any non-empty cells in the visible cells in that column (discounting the header), then keep that column visible. If there are only empty cells in the visible cells in that column, then hide that column. If that's correct: Option Explicit Sub testcolumnhide() Dim myCol As Range Dim wks As Worksheet Dim myRng As Range Set wks = ActiveSheet With wks.AutoFilter.Range 'avoid the headers by coming down one Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0) End With For Each myCol In myRng.Columns myCol.EntireColumn.Hidden = CBool(Application.Subtotal(3, myCol) = 0) Next myCol End Sub ===== I think you'd have to do something in your code to make sure you were looking at just the visible cells. You could use .cells.specialcells(xlcelltypevisible), but =subtotal(3,...) ignores rows hidden by data|filter. So I used that. DawnTreader wrote: Hello i have a similar situation. i am using excel to import a crosstab query from access. i have a autofilter on the results in the spreadsheet. i want to be able to hide columns based on whether or not there is data below the title of the column, so if column e is empty from rows 2 to 65536 i want it to hide that column. i would like it to happen automattically after a filter from the autofilter is applied, but a button would do. if i filter a column using the auto filter some columns become "empty". yes there is still stuff there in the empty columns, but they are being filtered leaving gaps in the columns. is there a way to use this code to hide those columns that dont show anything? i tried just "reversing" the code to work on columns, but those columns arent really empty, so i the code doesnt work. here was my attempt: Sub testcolumnhide() Dim iColumn As Long Dim myRngtoCheck As Range Dim HowManyRows As Long HowManyRows = 65533 With ActiveSheet For iColumn = 5 To 250 Set myRngtoCheck = .Cells(4, iColumn).Resize(HowManyRows, 1) If Application.CountIf(myRngtoCheck, 0) = HowManyRows Then .Columns(iColumn).Hidden = True End If Next iColumn End With End Sub thanks for any and all help! "Dave Peterson" wrote: How about: Option Explicit Sub Test_Hide_Rows2() Dim iRow As Long Dim myRngToCheck As Range Dim HowManyCols As Long HowManyCols = 12 'D to O is 12 columns With ActiveSheet For iRow = 8 To 359 'All Rows Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols) If Application.CountIf(myRngToCheck, 0) = HowManyCols Then 'all 0! .Rows(iRow).Hidden = True End If Next iRow End With End Sub AJ Master wrote: On Apr 9, 4:42 pm, OssieMac wrote: Try this. It ran in less than a second for me. Let me know how it goes. Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'AllRows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hideemptyrowsin a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100rowsto determine whether to hide the entire row. The number ofrowshas grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'AllRows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ OssieMac, Thanks for your quick reply. I tried your code, but its hiding rows that it shouldn't. I have to check every column D thru O (ie. 4 to 15) to see if the value is something other than zero. If the value in each column (D thru O) has a zero value then I can hide the row. Any tweak that might fix your code? -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Empty Rows
Hello
thanks. you are the man! :) "Dave Peterson" wrote: I think you're doing... Filter the range look at each column in the filtered range if there are any non-empty cells in the visible cells in that column (discounting the header), then keep that column visible. If there are only empty cells in the visible cells in that column, then hide that column. If that's correct: Option Explicit Sub testcolumnhide() Dim myCol As Range Dim wks As Worksheet Dim myRng As Range Set wks = ActiveSheet With wks.AutoFilter.Range 'avoid the headers by coming down one Set myRng = .Resize(.Rows.Count - 1).Offset(1, 0) End With For Each myCol In myRng.Columns myCol.EntireColumn.Hidden = CBool(Application.Subtotal(3, myCol) = 0) Next myCol End Sub ===== I think you'd have to do something in your code to make sure you were looking at just the visible cells. You could use .cells.specialcells(xlcelltypevisible), but =subtotal(3,...) ignores rows hidden by data|filter. So I used that. DawnTreader wrote: Hello i have a similar situation. i am using excel to import a crosstab query from access. i have a autofilter on the results in the spreadsheet. i want to be able to hide columns based on whether or not there is data below the title of the column, so if column e is empty from rows 2 to 65536 i want it to hide that column. i would like it to happen automattically after a filter from the autofilter is applied, but a button would do. if i filter a column using the auto filter some columns become "empty". yes there is still stuff there in the empty columns, but they are being filtered leaving gaps in the columns. is there a way to use this code to hide those columns that dont show anything? i tried just "reversing" the code to work on columns, but those columns arent really empty, so i the code doesnt work. here was my attempt: Sub testcolumnhide() Dim iColumn As Long Dim myRngtoCheck As Range Dim HowManyRows As Long HowManyRows = 65533 With ActiveSheet For iColumn = 5 To 250 Set myRngtoCheck = .Cells(4, iColumn).Resize(HowManyRows, 1) If Application.CountIf(myRngtoCheck, 0) = HowManyRows Then .Columns(iColumn).Hidden = True End If Next iColumn End With End Sub thanks for any and all help! "Dave Peterson" wrote: How about: Option Explicit Sub Test_Hide_Rows2() Dim iRow As Long Dim myRngToCheck As Range Dim HowManyCols As Long HowManyCols = 12 'D to O is 12 columns With ActiveSheet For iRow = 8 To 359 'All Rows Set myRngToCheck = .Cells(iRow, 4).Resize(1, HowManyCols) If Application.CountIf(myRngToCheck, 0) = HowManyCols Then 'all 0! .Rows(iRow).Hidden = True End If Next iRow End With End Sub AJ Master wrote: On Apr 9, 4:42 pm, OssieMac wrote: Try this. It ran in less than a second for me. Let me know how it goes. Sub Test_Hide_Rows() Dim lngRow As Integer Application.ScreenUpdating = False With ActiveSheet For lngRow = 8 To 359 'AllRows If .Cells(lngRow, 4) = 0 Then If .Cells(lngRow, 4).End(xlToRight).Column 15 Then .Cells(lngRow, 4).EntireRow.Hidden = True End If End If Next lngRow End With Application.ScreenUpdating = True End Sub Regards, OssieMac "AJ Master" wrote: I have been using the code below to hideemptyrowsin a worksheet. Originally I was only evaluating 12 columns (one for each month of the year) in each of about 100rowsto determine whether to hide the entire row. The number ofrowshas grown to about 350 and now the code runs rather slow. About 7-10secs versus the 2 secs it used to take. Anyone with any ideas on how to improved the speed of this code?? Dim bytCol As Byte Dim lngRow As Integer Application.ScreenUpdating = False For lngRow = 8 To 359 'AllRows For bytCol = 4 To 15 'Columns D-O If ActiveSheet.Cells(lngRow, bytCol) < 0 Then Exit For If bytCol = 15 Then ActiveSheet.Rows(lngRow).Hidden = True Next bytCol Next lngRow Application.ScreenUpdating = True Thanks....AJ OssieMac, Thanks for your quick reply. I tried your code, but its hiding rows that it shouldn't. I have to check every column D thru O (ie. 4 to 15) to see if the value is something other than zero. If the value in each column (D thru O) has a zero value then I can hide the row. Any tweak that might fix your code? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding Various Empty Rows | Excel Discussion (Misc queries) | |||
Delete Rows with Empty Cells with empty column 1 | Excel Programming | |||
How do I sort with empty rows between none empty rows? | Excel Worksheet Functions | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
Hiding an empty row? | Excel Programming |