Optimize Loop

davahill

New Member
Joined
Jan 16, 2014
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi experts,
I need help trying to optimize the following loop. Can this be changed to non-looping method?

VBA Code:
With .Cells(Lrow, "A")
                If Not IsError(.Value) Then
                     If .Value Like "1.2.*" Then
                          If Not .Offset(1, 0).Value Like "#.*" And Not .Offset(1, 0).Value = "" Then
                            If Not .Offset(2, 0).Value Like "#.*" And Not .Offset(2, 0).Value = "" Then
                                If Not .Offset(3, 0).Value Like "#.*" And Not .Offset(3, 0).Value = "" Then
                                    If Not .Offset(-1, 0).Value Like "3.*" And Not .Offset(-1, 0).Value = "" Then
                                    If .Offset(0, 1) = "" Then
                                             .Offset(0, 1).Value = .Value & " " & .Offset(1, 0).Value & " " & .Offset(2, 0).Value & " " & .Offset(3, 0).Value
                                        End If
                                     End If
                                    End If
                                End If
                           End If
                    End If
                End If
            End With
Thanks for any assistance you can provide.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Not sure what you're asking. I see no loop, just a bunch of nested If's.

I'm thinking a Loop might be one way to shorten that though, provided you can count from -1 to 3 or the reverse (I see that Offset 0 is used, so could be OK?). Perhaps would need a Select Case block inside the loop as well. But if you're trying to avoid a loop then I don't understand.
 
Upvote 0
Can you tell us clearly in words what the code is supposed to be doing?
 
Upvote 0
Thanks Micron and Peter for your assistance. I've imported combined multiple PDF documents into an excel raw spreadsheet and all data is located in column A. The dynamic number of rows can be anywhere from 20,000 up to around 400,000. This portion of the code copies certain data that meets certain criteria into column B. This actually is combining or appending the raw data which is split into multiple lines which is actually supposed to be a single line of data. Specifically, it looks at column A where the value starts with "1.2." and (a) where the three cells immediately below are not empty and do not start with a number, (b) where the cell above it does not start with "3." and is not empty and (c) the cell immediately adjacent in column B is empty. Where all this criteria is satisfied, it will then combine the cell value with the values of the three cells below it while placing a space between each group of words or data and place this into column B next to that cell where the value starts with "1.2.". I actually have to do this for all data from 2 rows of data up to 12 rows of data because one single line of data may be split from 2 rows up to12 lines. This will satisfy the requirement where there are 3 lines of data that should actually be 1 combined line. I hope I didn't make my explanation too difficult.

Thank you both again for your responses.
 
Upvote 0
Thanks for the explanation. I assume you have asked this question is because the code is taking too long, especially when you have large data.
The actual structure of all those If .. End If blocks is basically good, even though it seems a lot of code. It is good in that as soon as one of the tests fail, all the rest of the tests do not need to get checked. That is a speed aid. There could be very marginal improvements in that structure by ..
  • Suppose for example that error values are very rare but it is fairly common to find a blank cell 2 rows below a 1.2. line. In that case it would be slightly better to move this
    If Not .Offset(2, 0).Value Like "#.*" And Not .Offset(2, 0).Value = "" Then up to be the first test and this
    If Not IsError(.Value) Then down to be the last test. The sooner that you can find something to make the tests fail, the faster you can move down to start checking the next line/section.

  • Splitting lines like this If Not a(i + 1, 1) Like "#.*" And Not a(i + 1, 1) = "" Then into two separate tests. Same reason as above, instead of doing both of the tests involved in this line, if the first test fails then you can save time by not bothering with the second test.
Having made those points, I doubt that you will need to fuss over them since the main reason that your code is slow is that it is continually accessing the worksheet to retrieve cell values (& occasionally to insert cell values. Interactions between code and worksheet are a speed bottleneck.

Try the code below with a copy of your worksheet. I suspect that it will be plenty fast enough, even on fairly large data. The main reason is that it only interacts with the worksheet 3 times (reads column A into memory, reads column B into memory, writes all the final results back into column B at the end) whereas for say 100,000 rows, your code could interact with the worksheet somewhere around half a million times, possibly more.

I hope that I have replicated the intention of your code lines.

VBA Code:
Sub davahill()
  Dim a As Variant, b As Variant
  Dim i As Long
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value
  b = Range("B1:B" & UBound(a)).Value
  i = 1
  Do
    If Not IsError(a(i, 1)) Then
      If a(i, 1) Like "1.2.*" Then
        If Not a(i + 1, 1) Like "#.*" And Not a(i + 1, 1) = "" Then
          If Not a(i + 2, 1) Like "#.*" And Not a(i + 2, 1) = "" Then
            If Not a(i + 3, 1) Like "#.*" And Not a(i + 3, 1) = "" Then
              If Not a(i - 1, 1) Like "3.*" And Not a(i - 1, 1) = "" Then
                If b(i, 1) = "" Then
                  b(i, 1) = a(i, 1) & " " & a(i + 1, 1) & " " & a(i + 2, 1) & " " & a(i + 3, 1)
                End If
                i = i + 3
              End If
            End If
          End If
        End If
      End If
    End If
    i = i + 1
  Loop Until i > UBound(a) - 3
  Range("B1").Resize(UBound(b)).Value = b
End Sub

Here are the results for my sample data. If the code does not work for you. Please give some sample data and expected results with XL2BB making sure that there is a variety of the various circumstances involved.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

davahill.xlsm
AB
1a
21.2. abc1.2. abc xyz abc d e f
3xyz
4abc
5d e f
6b
71.2. def
83.abc
9q
10w
11e
121.2. ghi
13
14s
15w
16x
171.2. jkl1.2. jkl one two three four five six
18one two
19three four
20five six
21
Sheet1
 
Upvote 0
Solution
Thanks a million Peter for your assistance and expertise. Your code and explanation not only works perfectly, but will assist me with future similiar tasks.
 
Upvote 0
You're welcome. Thanks for the follow-up. :)

In relation to the explanations, I want to note a slight error in my first bullet point above. The concept of putting the most likely test failure at the top and the least likely test failure at the bottom is perfectly valid as a general rule. However, I was wrong in relation to the application of that to your particular code. If there are any error values in your data, then that error test does need to go first otherwise an error value in the data would cause an error in the code itself if that test was moved down the list. :)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top