These both do the same work (different column on sheet4) and seem to have no speed advantage either way.

Is code 1 properly written or can it be made faster.

Howard

Sub Column_OneRng1()
Dim LRow As Long, i As Long
Dim varData As Variant

Application.ScreenUpdating = False
With Sheets("Sheet3")
LRow = .Cells(Rows.Count, 4).End(xlUp).Row
varData = .Range("A2:F" & LRow)
For i = 1 To UBound(varData)
If varData(i, 4) < 150 Then
Sheets("Sheet4").Cells(Rows.Count, 1) _
.End(xlUp)(2) = varData(i, 1)

Sheets("Sheet4").Cells(Rows.Count, 3) _
.End(xlUp)(2) = " Type-" & varData(i, 5)

Sheets("Sheet4").Cells(Rows.Count, 5) _
.End(xlUp)(2) = " Age-" & varData(i, 6)

End If
Next
End With

Application.ScreenUpdating = True

End Sub



Sub Column_OneRng2()
Dim OneRng As Range
Dim c As Range

Set OneRng = Sheets("Sheet3").Range("D2:D" & Cells(Rows.Count, "D").End(xlUp).Row)

For Each c In OneRng
If c < 150 Then
Sheets("Sheet4").Range("B" & Rows.Count).End(xlUp)(2) = c.Offset(, -3)
Sheets("Sheet4").Range("D" & Rows.Count).End(xlUp)(2) = " Type-" & c.Offset(, 1)
Sheets("Sheet4").Range("F" & Rows.Count).End(xlUp)(2) = " Age-" & c.Offset(, 2)
End If
Next
End Sub

Hi Howard,

Am Wed, 22 Apr 2015 05:05:42 -0700 (PDT) schrieb L. Howard:

For i = 1 To UBound(varData)
If varData(i, 4) < 150 Then
Sheets("Sheet4").Cells(Rows.Count, 1) _
.End(xlUp)(2) = varData(i, 1)

Sheets("Sheet4").Cells(Rows.Count, 3) _
.End(xlUp)(2) = " Type-" & varData(i, 5)

Sheets("Sheet4").Cells(Rows.Count, 5) _
.End(xlUp)(2) = " Age-" & varData(i, 6)

perhaps one thing: Calculate the destination row only once

For i = 1 To UBound(varData)
If varData(i, 4) < 150 Then
DestRow = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheets("Sheet4").Cells(DestRow, 1) = varData(i, 1)
Sheets("Sheet4").Cells(DestRow, 3) = " Type-" & varData(i, 5)
Sheets("Sheet4").Cells(DestRow, 5) = " Age-" & varData(i, 6)
End If
Next

Or declare dest as range and try it this way:

For i = 1 To UBound(varData)
If varData(i, 4) < 150 Then
Set Dest = Sheets("Sheet4").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Dest = varData(i, 1)
Dest.Offset(, 2) = " Type-" & varData(i, 5)
Dest.Offset(, 4) = " Age-" & varData(i, 6)
End If
Next


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Claus,

I gave both a try, did not seem to run much, if any faster.

Seems like it should but at least I know they are a better code choice.

Thanks,
Howard

You *might* see some speed improvement if you load the values into an
output array that gets 'dumped' into Sheet4 in one shot. This, of
course, depends how much data you're working with.

Only other thing I see (has nothing to do with speed!) is that you
specify the start index for your loop as 1 instead of using
LBound(varData), which begs me to ask "why?"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, April 22, 2015 at 10:20:30 AM UTC-7, GS wrote:
You *might* see some speed improvement if you load the values into an
output array that gets 'dumped' into Sheet4 in one shot. This, of
course, depends how much data you're working with.

Only other thing I see (has nothing to do with speed!) is that you
specify the start index for your loop as 1 instead of using
LBound(varData), which begs me to ask "why?"!

--
Garry


The code is a modified version of one Claus wrote for a previous issue.

From my skill level it never occurred to me to question that.

Howard

Hi Howard,

Am Wed, 22 Apr 2015 08:22:37 -0700 (PDT) schrieb L. Howard:

I gave both a try, did not seem to run much, if any faster.

how many rows are in the sheet? If there are a big amount of rows you
could filter for <150 and copy the filtered ranges to the other sheet.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Howard,

Am Wed, 22 Apr 2015 11:01:18 -0700 (PDT) schrieb L. Howard:

The code is a modified version of one Claus wrote for a previous issue.

if I write a range to an array the array has base 1. Therefore I use 1
instead of LBound


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Wednesday, April 22, 2015 at 10:20:30 AM UTC-7, GS wrote:
You *might* see some speed improvement if you load the values into
an output array that gets 'dumped' into Sheet4 in one shot. This,
of course, depends how much data you're working with.

Only other thing I see (has nothing to do with speed!) is that you
specify the start index for your loop as 1 instead of using
LBound(varData), which begs me to ask "why?"!

--
Garry


The code is a modified version of one Claus wrote for a previous
issue.

From my skill level it never occurred to me to question that.

Howard

In this case, as it happens, LBound(varData)=1 but it's considered good
practice to *not* hard-code counters so code is lower maintenance AND
more flexible.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Howard,

Am Wed, 22 Apr 2015 08:22:37 -0700 (PDT) schrieb L. Howard:

I gave both a try, did not seem to run much, if any faster.

how many rows are in the sheet? If there are a big amount of rows you
could filter for <150 and copy the filtered ranges to the other
sheet.

That would certainly obviate the need for the If..Then construct since
the data will meet its criteria. I'm thinking, though, that for large
amounts it would be more efficient to load an output array and dump it
in one shot...

1. load the filtered data into a variant
2. Redim an output array (1 To UBound(varData), 1 To 5) and load it
using the Index() function...

With Application
.Index(vaDataOut(0, 1)) = .Index(varData(0, 1))
.Index(vaDataOut(0, 3)) = .Index(varData(0, 5))
.Index(vaDataOut(0, 5)) = .Index(varData(0, 6))
End With

3. dump vaDataOut into target sheet...

Dim rngOut As Range
Set rngOut = Sheets("Sheet4").Cells(1).End(xlUp)(2)
rngOut.Resize(UBound(vaDataOut, UBound(vaDataOut, 2)) = vaDataOut

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Am Wed, 22 Apr 2015 14:36:59 -0400 schrieb GS:

That would certainly obviate the need for the If..Then construct since
the data will meet its criteria. I'm thinking, though, that for large
amounts it would be more efficient to load an output array and dump it
in one shot...

you could filter column D for <150 and then copy Col A to Col A, Col E
to Col C and Col F to Col E. There is no need for an IF..Then construct.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Garry,

Am Wed, 22 Apr 2015 14:36:59 -0400 schrieb GS:

That would certainly obviate the need for the If..Then construct
since the data will meet its criteria. I'm thinking, though, that
for large amounts it would be more efficient to load an output
array and dump it in one shot...

you could filter column D for <150 and then copy Col A to Col A, Col
E to Col C and Col F to Col E. There is no need for an IF..Then
construct.


Regards
Claus B.

That's what I said! (Or more explicit, is what "obviate" means)

However, you still need to define the target range...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Wednesday, April 22, 2015 at 11:05:30 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Wed, 22 Apr 2015 08:22:37 -0700 (PDT) schrieb L. Howard:

I gave both a try, did not seem to run much, if any faster.

how many rows are in the sheet? If there are a big amount of rows you
could filter for <150 and copy the filtered ranges to the other sheet.


Regards
Claus B.

Hi Claus,

I was using about 1100 rows for testing, took about 2-3 seconds. Did not see it a major problem, was surprised my code and the revised code you offered took about the same time. More often the speed difference is huge and I was just assuming that would be the case here.

Writing an array code would be okay, if you want, would be of some help in my dismal effort to understand and write them myself. But not necessary.

Howard

On Wednesday, April 22, 2015 at 12:20:57 PM UTC-7, L. Howard wrote:
On Wednesday, April 22, 2015 at 11:05:30 AM UTC-7, Claus Busch wrote:
Hi Howard,

Am Wed, 22 Apr 2015 08:22:37 -0700 (PDT) schrieb L. Howard:

I gave both a try, did not seem to run much, if any faster.

how many rows are in the sheet? If there are a big amount of rows you
could filter for <150 and copy the filtered ranges to the other sheet.


Regards
Claus B.

Hi Claus,

I was using about 1100 rows for testing, took about 2-3 seconds. Did not see it a major problem, was surprised my code and the revised code you offered took about the same time. More often the speed difference is huge and I was just assuming that would be the case here.

Writing an array code would be okay, if you want, would be of some help in my dismal effort to understand and write them myself. But not necessary.

Howard

Forgot, yes, the filter thing is something I'll give a shot at. That seems to be a useful tool as I get to understand it more.

Thanks,
Howard

Writing an array code would be okay, if you want, would be of some
help in my dismal effort to understand and write them myself.

It's in making the effort that rewards prevail. I laid out a concept
for y'all in case anyone wanted to go there.

Given the time you post it's probably not needed, but might be when/if
the amount of data grows over time...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi again,

Am Wed, 22 Apr 2015 12:23:25 -0700 (PDT) schrieb L. Howard:

Forgot, yes, the filter thing is something I'll give a shot at. That seems to be a useful tool as I get to understand it more.

and "Age- " and "Type- " you can insert with custom numberformat


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional