Efficient looping
Hi
I have a single column with lots of rows (over 1000s) containing single digit number eg 0, 1, 2, 3 etc. What i need is an efficient code to replace selection and then offset. eg Range("C3").select do while selection <"" if activecell.value = 1 then 'do x endif activecell.offset(1,0).select loop The above code can take a long time to print. Any help appreciated. Thks Kz |
Efficient looping
On Thu, 8 Sep 2011 02:55:46 -0700 (PDT), Kieranz wrote:
Hi I have a single column with lots of rows (over 1000s) containing single digit number eg 0, 1, 2, 3 etc. What i need is an efficient code to replace selection and then offset. eg Range("C3").select do while selection <"" if activecell.value = 1 then 'do x endif activecell.offset(1,0).select loop The above code can take a long time to print. Any help appreciated. Thks Kz There is rarely any requirement to Select a cell, so you could do something like this: Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg 'do x Next c If you are only going to do something if the cell value = 1, then Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg If c.Value = 1 Then 'do x Next c End Sub In addition, if you are going to do different "x's" depending on the contents of the cell, then consider: Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg Select Case c.Value Case Is = 1 'do x Case Is = 2 'do y Case Else 'do z End Select Next c |
Efficient looping
Hi Ron
What i need to do is having found "1" that row must be hidden then move down next row, check and if "1" then hide that row and so on. but this iteration takes a long time. I had read somewhere it could be done much faster similar to eg like Find dialog wherein you can find all in one go do a Control + A to select all found and then do the necessary like hide all found rows in one go. Rgds Kz |
Efficient looping
In message of Thu, 8 Sep
2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld writes On Thu, 8 Sep 2011 02:55:46 -0700 (PDT), Kieranz wrote: Hi I have a single column with lots of rows (over 1000s) containing single digit number eg 0, 1, 2, 3 etc. What i need is an efficient code to replace selection and then offset. eg [snip] Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg If c.Value = 1 Then 'do x Next c End Sub For this case, I would do something like Dim c as Range, FirstAddress As String set c = Range("c3", Cells(3, 3).End(xlDown)).find(What:=1) If Not c Is Nothing Then FirstAddress = c.Address Do ' do x Set c = .FindNext(c) Loop While c.Address < FirstAddress End If I prefer that loop because it only matches cells where something is to be done. I infer you used For Each for symmetry with other examples. OTOH, you may have information that your construct takes less time. I would suspect not - fewer code lines are executed - but have not measured. In addition, if you are going to do different "x's" depending on the contents of the cell, then consider: Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg Select Case c.Value Case Is = 1 Why do you use "Case Is = 1" in preference to "Case 1"? I infer that you use "Is comparisonoperator expression" rather than "expression" because the former is more flexible and the latter duplicates the functionality of the former. 'do x Case Is = 2 'do y Case Else 'do z End Select Next c -- Walter Briscoe |
Efficient looping
On Thu, 8 Sep 2011 07:55:31 -0700 (PDT), Kieranz wrote:
Hi Ron What i need to do is having found "1" that row must be hidden then move down next row, check and if "1" then hide that row and so on. but this iteration takes a long time. I had read somewhere it could be done much faster similar to eg like Find dialog wherein you can find all in one go do a Control + A to select all found and then do the necessary like hide all found rows in one go. Rgds Kz It is always much more helpful to supply all of your requirements in your first post. You are more likely to obtain pertinent responses that way. As would have been the case here. Since what you want to do is hide all the rows that contain a 1, using auto-filter would be much quicker. Dim rg As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) Set rg = rg.Offset(rowoffset:=-1).Resize(rowsize:=rg.Rows.Count + 1) rg.AutoFilter Field:=1, Criteria1:="<1" Note that in order to use this, the first row of the range will never be hidden; so we have to expand it by one. If there is a label in C2, you could set up the range that way initially. Also note there is an optional argument for the AutoFilter to hide the dropdown box. |
Efficient looping
On Thu, 8 Sep 2011 16:40:09 +0100, Walter Briscoe wrote:
In message of Thu, 8 Sep 2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld writes On Thu, 8 Sep 2011 02:55:46 -0700 (PDT), Kieranz wrote: Hi I have a single column with lots of rows (over 1000s) containing single digit number eg 0, 1, 2, 3 etc. What i need is an efficient code to replace selection and then offset. eg [snip] Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg If c.Value = 1 Then 'do x Next c End Sub For this case, I would do something like Dim c as Range, FirstAddress As String set c = Range("c3", Cells(3, 3).End(xlDown)).find(What:=1) If Not c Is Nothing Then FirstAddress = c.Address Do ' do x Set c = .FindNext(c) Loop While c.Address < FirstAddress End If I prefer that loop because it only matches cells where something is to be done. I infer you used For Each for symmetry with other examples. OTOH, you may have information that your construct takes less time. I would suspect not - fewer code lines are executed - but have not measured. I haven't measured that either, but I suspect whether it is faster or not may depend on the nature and amount of the data In addition, if you are going to do different "x's" depending on the contents of the cell, then consider: Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg Select Case c.Value Case Is = 1 Why do you use "Case Is = 1" in preference to "Case 1"? I infer that you use "Is comparisonoperator expression" rather than "expression" because the former is more flexible and the latter duplicates the functionality of the former. It is always difficult for me to respond comprehensively to a question where incomplete specifications are provided. In this case, it turns out that the "do x" refers to "hide the row", suggesting a very different solution. After discovering that additional requirement, I suggest the AutoFilter, possible with the dropdown box made invisible. If he wants to move those visible rows to another sheet, I would then suggest the Advanced Filter, which has that capability. |
Efficient looping
On Sep 8, 6:14*pm, Ron Rosenfeld wrote:
On Thu, 8 Sep 2011 16:40:09 +0100, Walter Briscoe wrote: In message of Thu, 8 Sep 2011 10:23:58 in microsoft.public.excel.programming, Ron Rosenfeld writes On Thu, 8 Sep 2011 02:55:46 -0700 (PDT), Kieranz wrote: Hi I have a single column with lots of rows (over 1000s) containing single digit number eg 0, 1, 2, 3 etc. What i need is an efficient code to replace selection and then offset. eg [snip] Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg * *If c.Value = 1 Then 'do x Next c End Sub For this case, I would do something like Dim c as Range, FirstAddress As String set c = Range("c3", Cells(3, 3).End(xlDown)).find(What:=1) If Not c Is Nothing Then * *FirstAddress = c.Address * *Do * * * *' do x * * * *Set c = .FindNext(c) * *Loop While c.Address < FirstAddress End If I prefer that loop because it only matches cells where something is to be done. I infer you used For Each for symmetry with other examples. OTOH, you may have information that your construct takes less time. I would suspect not - fewer code lines are executed - but have not measured. I haven't measured that either, but I suspect whether it is faster or not may depend on the nature and amount of the data In addition, if you are going to do different "x's" depending on the contents of the cell, then consider: Dim rg As Range, c As Range Set rg = Range("c3", Cells(3, 3).End(xlDown)) For Each c In rg * *Select Case c.Value * * * *Case Is = 1 Why do you use "Case Is = 1" in preference to "Case 1"? I infer that you use "Is comparisonoperator expression" rather than "expression" because the former is more flexible and the latter duplicates the functionality of the former. It is always difficult for me to respond comprehensively to a question where incomplete specifications are provided. In this case, it turns out that the "do x" refers to "hide the row", suggesting a very different solution. After discovering that additional requirement, I suggest the AutoFilter, possible with the dropdown box made invisible. If he wants to move those visible rows to another sheet, I would then suggest the Advanced Filter, which has that capability. Hi Ron, Walter My apo being a newbie. However both of you given me food for thought. 1. For each next 2. Select case 3. do loop 4. Find FindNext 5. Auto filtering I will experiment although i was thinking more in terms of minimising hitting VBA and Excel, as i understand that's what slows or extends the time taken. Note also that when i use the find dialog (Ctrl+F) then Ctrl A to select all, it seems much much faster on a bigger data. More like instant! Thks a million for extending my knowledge. Rgds Kz |
Efficient looping
On Thu, 8 Sep 2011 09:39:32 -0700 (PDT), Kieranz wrote:
Hi Ron, Walter My apo being a newbie. However both of you given me food for thought. 1. For each next 2. Select case 3. do loop 4. Find FindNext 5. Auto filtering I will experiment although i was thinking more in terms of minimising hitting VBA and Excel, as i understand that's what slows or extends the time taken. Note also that when i use the find dialog (Ctrl+F) then Ctrl A to select all, it seems much much faster on a bigger data. More like instant! Thks a million for extending my knowledge. Rgds Kz Glad to help. But for the specific problem of hiding rows containing "1" in Column C, the AutoFilter (or, if you want to move the visible rows elsewhere, AdvancedFilter) will be the fastest. |
Efficient looping
I prefer that loop because it only matches cells where something is to
be done. I infer you used For Each for symmetry with other examples. OTOH, you may have information that your construct takes less time. I would suspect not - fewer code lines are executed - but have not measured. I haven't measured that either, but I suspect whether it is faster or not may depend on the nature and amount of the data Assuming your data is constants, not formulas (although I can modify this for that case), this will probably be one of the fastest methods to hide the rows for cells with a number 1 in them - and notice, no loops whatsoever... Sub HideRowsWithOnes() With Columns("A") .Replace 1, "=1", xlWhole .SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True .Replace "=", "", xlPart End With End Sub Rick Rothstein (MVP - Excel) |
Efficient looping
On Sep 8, 7:09*pm, "Rick Rothstein"
wrote: I prefer that loop because it only matches cells where something is to be done. I infer you used For Each for symmetry with other examples. OTOH, you may have information that your construct takes less time. I would suspect not - fewer code lines are executed - but have not measured. I haven't measured that either, but I suspect whether it is faster or not may depend on the nature and amount of the data Assuming your data is constants, not formulas (although I can modify this for that case), this will probably be one of the fastest methods to hide the rows for cells with a number 1 in them - and notice, no loops whatsoever.... Sub HideRowsWithOnes() * With Columns("A") * * .Replace 1, "=1", xlWhole * * .SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True * * .Replace "=", "", xlPart * End With End Sub Rick Rothstein (MVP - Excel) Hi Ron, tried the autofilter and works like a charm. Thks. Learnt something new! Rick - i will try this out, very unusual to say the least, how would u do it if the cell contained a formula. Will give it a shot over the weekend and chk back on Monday no internet access!!! Thks to both of u. Hav a lovely weekend Kz |
Efficient looping
Rick - i will try this out, very unusual to say the least
Yes, I know the approach is "out of the box", but it is quite fast when executing (Excel appears to be optimized "underneath it all" for replacements and the SpecialCells function). While the code I posted originally should work fine for you, I did leave out one statement that should be in it to prevent an error from occurring just in case you chose to run the code when there are no 1's in the data. Sub HideRowsWithOnes() With Columns("A") .Replace 1, "=1", xlWhole On Error Resume Next .SpecialCells(xlCellTypeFormulas).EntireRow.Hidden = True .Replace "=", "", xlPart End With End Sub how would u do it if the cell contained a formula. Same underlying method (just as fast though), but just a touch more work if the data was produced by formulas as opposed being constant values. Sub HideRowsWithOnes() Dim StartRow As Long, LastRow As Long, UnusedColumn As Long StartRow = 2 LastRow = Cells(Rows.Count, "A").End(xlUp).Row UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1 Application.ScreenUpdating = False Cells(StartRow, UnusedColumn).Resize(LastRow).Value = _ Cells(StartRow, "A").Resize(LastRow).Value On Error Resume Next With Columns(UnusedColumn) .Replace "1", "", xlWhole .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True .EntireColumn.Clear End With Application.ScreenUpdating = True End Sub Note that with this method, it is necessary to specify the start row for the data (headers, if any, are constants and must be stepped over). There is a StartRow variable at the beginning of the code where you can specify this value (I set it to 2 in my code assuming there was a header row... change if necessary). Rick Rothstein (MVP - Excel) |
All times are GMT +1. The time now is 06:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com