Setting data range and speeding up VBA code

[artz]

Hi,

I have some simple VBA code which does not work the way I want. It's probably something simple. First, in the code

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

I would like to start the row count at row B3 not row B1; tried several options but all returned errors.

Sub Test2()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To LastRow
Range("M" & i).Value = "dog"
    If Range("M" & i).Value = "dog" Then Range("N" & i).Value = "cat"
Next i
End Sub

Although this code returns an answer, it is very slow. It takes 5 minutes to process only about 20,000 rows. If anyone in the Forum could help move the range starting point to cell B3 and speed up execution time to a couple of seconds that would be great. And BTW, I'm not locked into this code, whatever works best will be used.

Any help would be very much appreciated. Am running this on Excel 2007 and Excel 2016.

Thanks,

-Art

 

[artz]

Hi Kenneth,

I ran the subs as you suggested: Sub Test3() and Sub BCrandoms(). For both, as soon as I hit F5, they executed instantly. My guess is tens of milliseconds) or less- not even hundreds.

My Dell workstation is fast - use it for SolidWorks and COMSOL FEA simulations. This is why I'm confused about the slow throughput with some of the code I presented.

Would it make sense to send you a datafile in a private message for you to try out my calculations with my dataset?

I'd still like to use VBA vs. cell formulas for several reasons.

What do you suggest to move forward.?

Thanks,

-Art

 

[Kenneth Hobson]

That kind of PM would be a violation of FAQ rule #4 .

You could make a simple example file and post the link from a free shared site like dropbox. Some forum members will not view files like that.

Another option is to post a screen snip/html with formulas. This forum has links to different addins that can make creating the html code easy. The links to addins and test forum can be found at: https://www.mrexcel.com/forum/about-board/508133-attachments.html

Arrays and filter methods are fast. Writing once instead of many times is faster. The link that you posted can help speed with many writes but that only helps a bit. I use similar methods too depending on my solution.

 

[artz]

Hi Kenneth,

I had an idea while I was driving today. The reason I have so many rows of data is due to the high sample rate of my oscilloscope. On the timescale that I am capturing data (2.5s/div) the scope sets the sample rate to 1000Hz giving me samples every millisecond. My data are in the less than 1 Hz range. This means the data are way oversampled.

What I can do is use my decimation macro to down sample to a more reasonable rate. I'll then retry the suggestions posted in this thread and see what that gets me. Also, I'll post a data snippet for you and others to look at.

How does that sound?

Thanks,

-Art

 

[artz]

Hi All,

Several good ideas percolated up from the various posters. I appreciate all the effort, thanks! Below is code for a working macro that for the moment, serves my purposes.

Sub Test3()
Dim LastRow As Long, i As Long
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To LastRow
    If Range("B" & i).Value >= Range("C" & i).Value And Range("B" & i - 1).Value >= Range("C" & i - 1).Value Then
    Range("D" & i).Value = 2.5
    ElseIf Range("B" & i).Value <= Range("C" & i).Value And Range("B" & i - 1).Value <= Range("C" & i - 1).Value Then
    Range("D" & i).Value = 0
    End If
Next i
End Sub

Also, a small data set to try out the macro:

Time	Bias	Signal	UpDown
0	2.00	2.08	
0.02	2.00	2.08	1.5
0.04	2.00	2.08	1.5
0.06	2.00	2.08	1.5
0.08	2.00	2.08	1.5
0.1	2.00	2.08	1.5
0.12	2.00	2.08	1.5
0.14	2.00	2.08	1.5
0.16	2.00	2.08	1.5
0.18	2.00	2.08	1.5
0.2	2.00	2.08	1.5
0.22	2.00	2.08	1.5
0.24	2.00	2.08	1.5
0.26	2.00	2.08	1.5
0.28	2.00	2.08	1.5
0.3	2.00	2.08	1.5
0.32	2.00	2.08	1.5
0.34	2.00	2.12	1.5
0.36	2.00	2.08	1.5
0.38	2.00	2.08	1.5
0.4	2.00	2.08	1.5
0.42	2.00	2.08	1.5
0.44	2.00	2.08	1.5
0.46	2.00	2.08	1.5
0.48	2.00	2.12	1.5
0.5	2.00	2.08	1.5
0.52	2.00	2.08	1.5
0.54	2.00	2.08	1.5
0.56	2.00	2.08	1.5
0.58	2.00	2.08	1.5
0.6	2.00	2.08	1.5
0.62	2.00	2.08	1.5
0.64	2.00	2.08	1.5
0.66	2.00	2.08	1.5
0.68	2.00	2.08	1.5
0.7	2.00	2.08	1.5
0.72	2.00	2.08	1.5
0.74	2.00	2.08	1.5
0.76	2.00	2.08	1.5
0.78	2.00	2.08	1.5
0.8	2.00	2.08	1.5
0.82	2.00	2.12	1.5
0.84	2.00	2.08	1.5
0.86	2.00	2.08	1.5
0.88	2.00	2.08	1.5
0.9	2.00	2.08	1.5
0.92	2.00	2.08	1.5
0.94	2.00	2.08	1.5
0.96	2.00	2.08	1.5
0.98	2.00	2.08	1.5
1	2.00	2.08	1.5
1.02	2.00	2.08	1.5
1.04	2.00	2.04	1.5
1.06	2.00	2	1.5
1.08	2.00	1.96	2.5
1.1	2.00	1.96	2.5
1.12	2.00	1.92	2.5
1.14	2.00	1.88	2.5
1.16	2.00	1.84	2.5
1.18	2.00	1.8	2.5
1.2	2.00	1.76	2.5
1.22	2.00	1.72	2.5
1.24	2.00	1.68	2.5
1.26	2.00	1.64	2.5
1.28	2.00	1.6	2.5
1.3	2.00	1.56	2.5
1.32	2.00	1.52	2.5
1.34	2.00	1.52	2.5
1.36	2.00	1.48	2.5
1.38	2.00	1.44	2.5
1.4	2.00	1.44	2.5
1.42	2.00	1.44	2.5
1.44	2.00	1.44	2.5
1.46	2.00	1.48	2.5
1.48	2.00	1.48	2.5
1.5	2.00	1.48	2.5
1.52	2.00	1.48	2.5
1.54	2.00	1.48	2.5
1.56	2.00	1.52	2.5
1.58	2.00	1.52	2.5
1.6	2.00	1.48	2.5
1.62	2.00	1.52	2.5
1.64	2.00	1.48	2.5
1.66	2.00	1.48	2.5
1.68	2.00	1.44	2.5
1.7	2.00	1.44	2.5
1.72	2.00	1.4	2.5
1.74	2.00	1.4	2.5
1.76	2.00	1.36	2.5
1.78	2.00	1.32	2.5
1.8	2.00	1.32	2.5
1.82	2.00	1.28	2.5
1.84	2.00	1.24	2.5
1.86	2.00	1.2	2.5
1.88	2.00	1.2	2.5
1.9	2.00	1.16	2.5
1.92	2.00	1.16	2.5
1.94	2.00	1.12	2.5
1.96	2.00	1.12	2.5
1.98	2.00	1.08	2.5
2	2.00	1.08	2.5
2.02	2.00	1.08	2.5
2.04	2.00	1.08	2.5
2.06	2.00	1.08	2.5
2.08	2.00	1.12	2.5
2.1	2.00	1.08	2.5
2.12	2.00	1.12	2.5
2.14	2.00	1.12	2.5
2.16	2.00	1.12	2.5
2.18	2.00	1.16	2.5
2.2	2.00	1.2	2.5
2.22	2.00	1.2	2.5
2.24	2.00	1.2	2.5
2.26	2.00	1.2	2.5
2.28	2.00	1.24	2.5
2.3	2.00	1.24	2.5
2.32	2.00	1.28	2.5
2.34	2.00	1.28	2.5
2.36	2.00	1.28	2.5
2.38	2.00	1.32	2.5
2.4	2.00	1.32	2.5
2.42	2.00	1.32	2.5
2.44	2.00	1.36	2.5
2.46	2.00	1.36	2.5
2.48	2.00	1.36	2.5
2.5	2.00	1.4	2.5
2.52	2.00	1.4	2.5
2.54	2.00	1.4	2.5
2.56	2.00	1.4	2.5
2.58	2.00	1.4	2.5
2.6	2.00	1.44	2.5
2.62	2.00	1.44	2.5
2.64	2.00	1.44	2.5
2.66	2.00	1.48	2.5
2.68	2.00	1.48	2.5
2.7	2.00	1.48	2.5
2.72	2.00	1.48	2.5
2.74	2.00	1.48	2.5
2.76	2.00	1.48	2.5
2.78	2.00	1.48	2.5
2.8	2.00	1.52	2.5
2.82	2.00	1.52	2.5
2.84	2.00	1.52	2.5
2.86	2.00	1.52	2.5
2.88	2.00	1.56	2.5
2.9	2.00	1.6	2.5
2.92	2.00	1.6	2.5
2.94	2.00	1.64	2.5
2.96	2.00	1.72	2.5
2.98	2.00	1.8	2.5
3	2.00	1.88	2.5
3.02	2.00	1.96	2.5
3.04	2.00	2.04	1.5
3.06	2.00	2.12	1.5
3.08	2.00	2.2	1.5
3.1	2.00	2.28	1.5
3.12	2.00	2.36	1.5
3.14	2.00	2.44	1.5
3.16	2.00	2.48	1.5
3.18	2.00	2.52	1.5
3.2	2.00	2.56	1.5
3.22	2.00	2.56	1.5
3.24	2.00	2.6	1.5
3.26	2.00	2.6	1.5
3.28	2.00	2.64	1.5
3.3	2.00	2.64	1.5
3.32	2.00	2.64	1.5
3.34	2.00	2.6	1.5
3.36	2.00	2.64	1.5
3.38	2.00	2.6	1.5
3.4	2.00	2.6	1.5
3.42	2.00	2.6	1.5
3.44	2.00	2.6	1.5
3.46	2.00	2.6	1.5
3.48	2.00	2.6	1.5
3.5	2.00	2.6	1.5
3.52	2.00	2.56	1.5
3.54	2.00	2.56	1.5
3.56	2.00	2.6	1.5
3.58	2.00	2.56	1.5
3.6	2.00	2.56	1.5
3.62	2.00	2.56	1.5
3.64	2.00	2.56	1.5
3.66	2.00	2.56	1.5
3.68	2.00	2.56	1.5
3.7	2.00	2.52	1.5
3.72	2.00	2.56	1.5
3.74	2.00	2.52	1.5
3.76	2.00	2.56	1.5
3.78	2.00	2.52	1.5
3.8	2.00	2.52	1.5
3.82	2.00	2.52	1.5
3.84	2.00	2.52	1.5
3.86	2.00	2.52	1.5
3.88	2.00	2.52	1.5
3.9	2.00	2.52	1.5
3.92	2.00	2.52	1.5
3.94	2.00	2.52	1.5
3.96	2.00	2.48	1.5
3.98	2.00	2.52	1.5
4	2.00	2.48	1.5
4.02	2.00	2.48	1.5
4.04	2.00	2.48	1.5
4.06	2.00	2.48	1.5
4.08	2.00	2.48	1.5
4.1	2.00	2.48	1.5
4.12	2.00	2.48	1.5
4.14	2.00	2.48	1.5
4.16	2.00	2.44	1.5
4.18	2.00	2.44	1.5
4.2	2.00	2.48	1.5
4.22	2.00	2.44	1.5
4.24	2.00	2.44	1.5
4.26	2.00	2.44	1.5
4.28	2.00	2.44	1.5
4.3	2.00	2.44	1.5
4.32	2.00	2.44	1.5
4.34	2.00	2.44	1.5
4.36	2.00	2.44	1.5
4.38	2.00	2.44	1.5
4.4	2.00	2.4	1.5
4.42	2.00	2.44	1.5
4.44	2.00	2.4	1.5
4.46	2.00	2.4	1.5
4.48	2.00	2.4	1.5
4.5	2.00	2.4	1.5
4.52	2.00	2.4	1.5
4.54	2.00	2.4	1.5
4.56	2.00	2.4	1.5
4.58	2.00	2.4	1.5
4.6	2.00	2.36	1.5
4.62	2.00	2.36	1.5
4.64	2.00	2.36	1.5
4.66	2.00	2.36	1.5
4.68	2.00	2.36	1.5
4.7	2.00	2.36	1.5
4.72	2.00	2.32	1.5
4.74	2.00	2.32	1.5
4.76	2.00	2.32	1.5
4.78	2.00	2.32	1.5
4.8	2.00	2.32	1.5
4.82	2.00	2.28	1.5
4.84	2.00	2.24	1.5
4.86	2.00	2.24	1.5
4.88	2.00	2.24	1.5
4.9	2.00	2.2	1.5
4.92	2.00	2.16	1.5
4.94	2.00	2.12	1.5
4.96	2.00	2.12	1.5
4.98	2.00	2.08	1.5
5	2.00	2.08	1.5
5.02	2.00	2	1.5
5.04	2.00	1.96	2.5
5.06	2.00	1.92	2.5
5.08	2.00	1.88	2.5
5.1	2.00	1.84	2.5
5.12	2.00	1.8	2.5
5.14	2.00	1.76	2.5
5.16	2.00	1.72	2.5
5.18	2.00	1.72	2.5
5.2	2.00	1.68	2.5
5.22	2.00	1.64	2.5
5.24	2.00	1.56	2.5
5.26	2.00	1.56	2.5
5.28	2.00	1.56	2.5
5.3	2.00	1.48	2.5
5.32	2.00	1.48	2.5
5.34	2.00	1.44	2.5
5.36	2.00	1.44	2.5
5.38	2.00	1.4	2.5
5.4	2.00	1.4	2.5
5.42	2.00	1.36	2.5
5.44	2.00	1.36	2.5
5.46	2.00	1.32	2.5
5.48	2.00	1.36	2.5
5.5	2.00	1.36	2.5
5.52	2.00	1.36	2.5
5.54	2.00	1.36	2.5
5.56	2.00	1.36	2.5
5.58	2.00	1.36	2.5
5.6	2.00	1.36	2.5
5.62	2.00	1.4	2.5
5.64	2.00	1.4	2.5
5.66	2.00	1.4	2.5
5.68	2.00	1.4	2.5
5.7	2.00	1.4	2.5
5.72	2.00	1.4	2.5
5.74	2.00	1.4	2.5
5.76	2.00	1.36	2.5
5.78	2.00	1.4	2.5
5.8	2.00	1.4	2.5
5.82	2.00	1.4	2.5
5.84	2.00	1.36	2.5
5.86	2.00	1.4	2.5
5.88	2.00	1.4	2.5
5.9	2.00	1.4	2.5
5.92	2.00	1.4	2.5
5.94	2.00	1.44	2.5
5.96	2.00	1.44	2.5
5.98	2.00	1.44	2.5
6	2.00	1.44	2.5
6.02	2.00	1.48	2.5
6.04	2.00	1.48	2.5
6.06	2.00	1.52	2.5
6.08	2.00	1.56	2.5
6.1	2.00	1.56	2.5
6.12	2.00	1.56	2.5
6.14	2.00	1.6	2.5
6.16	2.00	1.56	2.5
6.18	2.00	1.6	2.5
6.2	2.00	1.6	2.5
6.22	2.00	1.6	2.5
6.24	2.00	1.6	2.5
6.26	2.00	1.6	2.5
6.28	2.00	1.64	2.5
6.3	2.00	1.64	2.5
6.32	2.00	1.68	2.5
6.34	2.00	1.68	2.5
6.36	2.00	1.68	2.5
6.38	2.00	1.68	2.5
6.4	2.00	1.68	2.5
6.42	2.00	1.68	2.5
6.44	2.00	1.72	2.5
6.46	2.00	1.72	2.5
6.48	2.00	1.72	2.5
6.5	2.00	1.72	2.5
6.52	2.00	1.76	2.5
6.54	2.00	1.76	2.5
6.56	2.00	1.76	2.5
6.58	2.00	1.72	2.5
6.6	2.00	1.76	2.5
6.62	2.00	1.76	2.5
6.64	2.00	1.76	2.5
6.66	2.00	1.8	2.5
6.68	2.00	1.8	2.5
6.7	2.00	1.84	2.5
6.72	2.00	1.88	2.5
6.74	2.00	1.88	2.5
6.76	2.00	1.96	2.5
6.78	2.00	1.96	2.5
6.8	2.00	2.04	1.5
6.82	2.00	2.08	1.5
6.84	2.00	2.12	1.5
6.86	2.00	2.2	1.5
6.88	2.00	2.24	1.5
6.9	2.00	2.32	1.5
6.92	2.00	2.36	1.5
6.94	2.00	2.44	1.5
6.96	2.00	2.48	1.5
6.98	2.00	2.52	1.5
7	2.00	2.56	1.5
7.02	2.00	2.6	1.5
7.04	2.00	2.6	1.5
7.06	2.00	2.6	1.5
7.08	2.00	2.64	1.5
7.1	2.00	2.64	1.5
7.12	2.00	2.6	1.5
7.14	2.00	2.6	1.5
7.16	2.00	2.6	1.5
7.18	2.00	2.64	1.5
7.2	2.00	2.6	1.5
7.22	2.00	2.6	1.5
7.24	2.00	2.56	1.5
7.26	2.00	2.56	1.5
7.28	2.00	2.56	1.5
7.3	2.00	2.56	1.5
7.32	2.00	2.56	1.5
7.34	2.00	2.56	1.5
7.36	2.00	2.56	1.5
7.38	2.00	2.56	1.5
7.4	2.00	2.56	1.5
7.42	2.00	2.52	1.5
7.44	2.00	2.52	1.5
7.46	2.00	2.52	1.5
7.48	2.00	2.52	1.5
7.5	2.00	2.52	1.5
7.52	2.00	2.52	1.5
7.54	2.00	2.52	1.5
7.56	2.00	2.52	1.5
7.58	2.00	2.48	1.5
7.6	2.00	2.52	1.5
7.62	2.00	2.52	1.5
7.64	2.00	2.48	1.5
7.66	2.00	2.48	1.5
7.68	2.00	2.48	1.5
7.7	2.00	2.52	1.5
7.72	2.00	2.48	1.5
7.74	2.00	2.52	1.5
7.76	2.00	2.48	1.5
7.78	2.00	2.48	1.5
7.8	2.00	2.48	1.5
7.82	2.00	2.48	1.5
7.84	2.00	2.48	1.5
7.86	2.00	2.48	1.5
7.88	2.00	2.48	1.5
7.9	2.00	2.48	1.5
7.92	2.00	2.48	1.5
7.94	2.00	2.44	1.5
7.96	2.00	2.44	1.5
7.98	2.00	2.44	1.5
8	2.00	2.44	1.5
8.02	2.00	2.44	1.5
8.04	2.00	2.44	1.5
8.06	2.00	2.44	1.5
8.08	2.00	2.44	1.5
8.1	2.00	2.4	1.5
8.12	2.00	2.44	1.5
8.14	2.00	2.44	1.5
8.16	2.00	2.44	1.5
8.18	2.00	2.44	1.5
8.2	2.00	2.44	1.5
8.22	2.00	2.4	1.5
8.24	2.00	2.4	1.5
8.26	2.00	2.4	1.5
8.28	2.00	2.4	1.5
8.3	2.00	2.36	1.5
8.32	2.00	2.4	1.5
8.34	2.00	2.4	1.5
8.36	2.00	2.36	1.5
8.38	2.00	2.36	1.5
8.4	2.00	2.36	1.5
8.42	2.00	2.36	1.5
8.44	2.00	2.36	1.5
8.46	2.00	2.36	1.5
8.48	2.00	2.36	1.5
8.5	2.00	2.32	1.5
8.52	2.00	2.32	1.5
8.54	2.00	2.36	1.5
8.56	2.00	2.32	1.5
8.58	2.00	2.32	1.5
8.6	2.00	2.32	1.5
8.62	2.00	2.32	1.5
8.64	2.00	2.32	1.5
8.66	2.00	2.32	1.5
8.68	2.00	2.32	1.5
8.7	2.00	2.28	1.5
8.72	2.00	2.28	1.5
8.74	2.00	2.28	1.5
8.76	2.00	2.28	1.5
8.78	2.00	2.24	1.5
8.8	2.00	2.24	1.5
8.82	2.00	2.2	1.5
8.84	2.00	2.2	1.5
8.86	2.00	2.12	1.5
8.88	2.00	2.08	1.5
8.9	2.00	2.04	1.5
8.92	2.00	2	1.5
8.94	2.00	1.96	2.5
8.96	2.00	1.92	2.5
8.98	2.00	1.88	2.5
9	2.00	1.84	2.5
9.02	2.00	1.76	2.5
9.04	2.00	1.72	2.5
9.06	2.00	1.68	2.5
9.08	2.00	1.64	2.5
9.1	2.00	1.64	2.5
9.12	2.00	1.6	2.5
9.14	2.00	1.56	2.5
9.16	2.00	1.52	2.5
9.18	2.00	1.52	2.5
9.2	2.00	1.52	2.5
9.22	2.00	1.52	2.5
9.24	2.00	1.48	2.5
9.26	2.00	1.48	2.5
9.28	2.00	1.48	2.5
9.3	2.00	1.48	2.5
9.32	2.00	1.48	2.5
9.34	2.00	1.48	2.5
9.36	2.00	1.48	2.5
9.38	2.00	1.52	2.5
9.4	2.00	1.52	2.5
9.42	2.00	1.52	2.5
9.44	2.00	1.52	2.5
9.46	2.00	1.52	2.5
9.48	2.00	1.52	2.5
9.5	2.00	1.56	2.5
9.52	2.00	1.52	2.5
9.54	2.00	1.56	2.5
9.56	2.00	1.56	2.5
9.58	2.00	1.56	2.5
9.6	2.00	1.56	2.5
9.62	2.00	1.56	2.5
9.64	2.00	1.56	2.5
9.66	2.00	1.56	2.5
9.68	2.00	1.56	2.5
9.7	2.00	1.56	2.5
9.72	2.00	1.56	2.5
9.74	2.00	1.52	2.5
9.76	2.00	1.52	2.5
9.78	2.00	1.56	2.5
9.8	2.00	1.56	2.5
9.82	2.00	1.56	2.5
9.84	2.00	1.56	2.5
9.86	2.00	1.56	2.5
9.88	2.00	1.56	2.5
9.9	2.00	1.56	2.5
9.92	2.00	1.56	2.5
9.94	2.00	1.56	2.5
9.96	2.00	1.6	2.5
9.98	2.00	1.56	2.5
10	2.00	1.56	2.5

Thanks again to all who helped.

-Art