Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Hi,
I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Harold,
I realized I wrote the code to delete rows, not to hide rows. I apologize for not reading your post carefully enough. Simply exchange the ..EntrieRow.Delete with .EntrieRow.Hidden = True. Best, Matt "Harold Good" wrote: Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Harold,
I've included the two separate ways below that you mention. In general, performing methods on an aggregate basis rather than a one-by-one basis is fastest, thus, you'll see the Union function below. In your situation, when you delete one-by-one, Excel may be recalculating after every deletion, so you may have up to 90 different recalculations on a one-by-one basis rather than 1 recalculation on the aggregate. You can loop one-by-one if you so desire because a loop of 90 items is practically negligible. (Also, I would comment the .EntireRow.Delete syntax below and uncomment the Debug.Print lines. Debug.Print will print to the Immediate Window (View | Immediate Window) and will allow you to see how the program is behaving prior to executing a deletion). Best, Matthew Herbert Sub TestDeleteBlanks() Dim rngEval As Range Dim rngDelete As Range Dim rngCell As Range Set rngEval = ActiveSheet.Range("F9:F98") Set rngDelete = rngEval.SpecialCells(xlCellTypeBlanks) 'Debug.Print rngDelete.Address rngDelete.EntireRow.Delete Set rngDelete = Nothing '-OR- For Each rngCell In rngEval.Cells If rngCell.Value = "" Then If rngDelete Is Nothing Then Set rngDelete = rngCell Else Set rngDelete = Union(rngDelete, rngCell) End If End If Next rngCell 'Debug.Print rngDelete.Address rngDelete.EntireRow.Delete End Sub "Harold Good" wrote: Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Thanks very much Matthew, I shall give this a try tomorrow!
Harold "Matthew Herbert" wrote in message ... Harold, I realized I wrote the code to delete rows, not to hide rows. I apologize for not reading your post carefully enough. Simply exchange the .EntrieRow.Delete with .EntrieRow.Hidden = True. Best, Matt "Harold Good" wrote: Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Thank you Don, I will try this out. So simple I need to study it to see what
all it's doing! Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Just look in the vba help index for SPECIALCELLS
-- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Thank you Don, I will try this out. So simple I need to study it to see what all it's doing! Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Much better to have collective operations rather than loops.
Also when running any macro working with a large data set or complex calculations consider setting the following off then resetting on after completion. Application.ScreenUpdating = False/True Application.Calculation = xlCalculationManual/xlCalculationAutomatic Application.EnableEvents = False/True The most significant of these is the first with only incremental gains from the others. -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "Don Guillett" wrote: Just look in the vba help index for SPECIALCELLS -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Thank you Don, I will try this out. So simple I need to study it to see what all it's doing! Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Thanks Ken for this great reminder. I knew about the screen updating but
thought it was only to make it look nicer during the process. I had no idea it'd impact speed. I did a rough timing of it to go thru the 90 cells, it took about 4 times longer with updating = true. Great suggestion. Harold "K_Macd" <kmacdonald "A_T" activ8 ''''''''''''''''''''''''''''''''D O T'''''''''''''''''''''''''''''''' net [S wrote in message ... Much better to have collective operations rather than loops. Also when running any macro working with a large data set or complex calculations consider setting the following off then resetting on after completion. Application.ScreenUpdating = False/True Application.Calculation = xlCalculationManual/xlCalculationAutomatic Application.EnableEvents = False/True The most significant of these is the first with only incremental gains from the others. -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "Don Guillett" wrote: Just look in the vba help index for SPECIALCELLS -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Thank you Don, I will try this out. So simple I need to study it to see what all it's doing! Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
This will select from F9 downwards to the end of the contiguous set of
non-blank cells: Private Sub Worksheet_Calculate() Dim cells As Range Dim cell As Range Application.EnableEvents = False Range("F9").Select Selection.End(xlDown).Select For Each cell In Range("F9:F" & Trim(Selection.Row)) If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub I think the solutions posted by others are superior, but I thought this was an interesting way of avoiding processing blank rows if you do need to do something more complex that needs a loop. Phil Hibbs. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Hi, I have tried this and the other suggestion offered by Matthew Herbert.
Neither seem to work and I think the problem might be that all cells in this F range have formulas in them. In the ones I want to hide, the formulas solve to "" so they appear blank to the user. But it appears that SpecialCells(xlCellTypeBlanks) does not count these as blank cells. If I delete the formula then the code below works. Is there a way to make this work so that if any cells in this range = "", they will be hidden? Thanks again for your help, Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Thanks for this Phil, I see this will avoid unnecessary looping which would
be helpful. So many different ways to do these things! I'll wait to see if anyone can help with the SpecialCells track I was pursuing. Harold "Phil Hibbs" wrote in message ... This will select from F9 downwards to the end of the contiguous set of non-blank cells: Private Sub Worksheet_Calculate() Dim cells As Range Dim cell As Range Application.EnableEvents = False Range("F9").Select Selection.End(xlDown).Select For Each cell In Range("F9:F" & Trim(Selection.Row)) If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub I think the solutions posted by others are superior, but I thought this was an interesting way of avoiding processing blank rows if you do need to do something more complex that needs a loop. Phil Hibbs. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Harold,
I created dummy values in column E and input an IF function in column F (e.g. =IF(E9="","",E9)). When I used the For Each loop (listed below), I didn't have any trouble hiding the rows. Test this code and see if you get the same results; otherwise, you might need to provide more details regarding your function, whether rows are already hidden, etc. Best, Matt Sub TestHideRows() Dim rngEval As Range Dim rngHide As Range Dim rngCell As Range Set rngEval = ActiveSheet.Range("F9:F98") For Each rngCell In rngEval.Cells If rngCell.Value = "" Then If rngHide Is Nothing Then Set rngHide = rngCell Else Set rngHide = Union(rngHide, rngCell) End If End If Next rngCell Debug.Print rngHide.Address rngHide.EntireRow.Hidden = True End Sub "Harold Good" wrote: Hi, I have tried this and the other suggestion offered by Matthew Herbert. Neither seem to work and I think the problem might be that all cells in this F range have formulas in them. In the ones I want to hide, the formulas solve to "" so they appear blank to the user. But it appears that SpecialCells(xlCellTypeBlanks) does not count these as blank cells. If I delete the formula then the code below works. Is there a way to make this work so that if any cells in this range = "", they will be hidden? Thanks again for your help, Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
code to hide rows based on criteria - but without looping
Thanks Matt, I had only tried your first one, not your second one. This
second one with the Union works great and is very fast. Thank you for introducing me to Union, and for your kind help as well. Now I'm off and running. Have a great day! Harold "Matthew Herbert" wrote in message ... Harold, I created dummy values in column E and input an IF function in column F (e.g. =IF(E9="","",E9)). When I used the For Each loop (listed below), I didn't have any trouble hiding the rows. Test this code and see if you get the same results; otherwise, you might need to provide more details regarding your function, whether rows are already hidden, etc. Best, Matt Sub TestHideRows() Dim rngEval As Range Dim rngHide As Range Dim rngCell As Range Set rngEval = ActiveSheet.Range("F9:F98") For Each rngCell In rngEval.Cells If rngCell.Value = "" Then If rngHide Is Nothing Then Set rngHide = rngCell Else Set rngHide = Union(rngHide, rngCell) End If End If Next rngCell Debug.Print rngHide.Address rngHide.EntireRow.Hidden = True End Sub "Harold Good" wrote: Hi, I have tried this and the other suggestion offered by Matthew Herbert. Neither seem to work and I think the problem might be that all cells in this F range have formulas in them. In the ones I want to hide, the formulas solve to "" so they appear blank to the user. But it appears that SpecialCells(xlCellTypeBlanks) does not count these as blank cells. If I delete the formula then the code below works. Is there a way to make this work so that if any cells in this range = "", they will be hidden? Thanks again for your help, Harold "Don Guillett" wrote in message ... How about a nice one liner Sub deleteblankrows() Range("a1:a6").SpecialCells(xlCellTypeBlanks).Enti reRow.hidden=true End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Harold Good" wrote in message ... Hi, I'd like to hide unused rows in a budget form based on a formula in Col F of any row in the range below that is equal to "". I know little about VBA, but the code below works, thhough it is too slow to do everytime it recalculates. Because Column F values are entered from a separate worksheet, I cannot use the Worksheet_Change Event. Private Sub Worksheet_Calculate() Dim cell As Range Application.EnableEvents = False For Each cell In Range("F9:F98") If cell.Value = "" Then cell.EntireRow.Hidden = True End If Next cell Application.EnableEvents = True End Sub What I've read elsewhere is to avoid Loops whenever possible. Since all the rows that do not equal "" are at the top, and all those that equal "" are at the bottom, is there a better way to do this using CountA, or SpecialCells? Thanks for any help you can offer, Harold |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code for Excel 2007 to hide rows based on sum of several rows not ina range | Excel Programming | |||
Macro to hide rows based on criteria | Excel Programming | |||
Code help, delete rows based on column criteria | Excel Discussion (Misc queries) | |||
Code to hide Entire row based on criteria | Excel Programming | |||
Code to hide rows based on cell contents | Excel Programming |