Find range for distinct items in column ?

[Fin]

Using Vbscript for an Excel 2010 worksheet, how can I find the start and end range of distinct values with a column ?

I have a worksheet with some 200k rows, and 45 columns. 20 of such columns contain rows indicating a color value that I need to set. I have code that works but when working on 200k rows it is incredibly slow.

Set objRange = objXLWs.UsedRange
iRows = objRange.Rows.Count
iColumns = objRange.Columns.Count

For iR = 2 To iRows
Select Case objRange.Item(iR, iC).Value
Case "Y"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 6 'yellow
Case "R"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 3 'red
Case "O"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 45 'orange
Case "T"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 42 'terquoise
End Select
Next

What I am thinking is that sorting the column, bringing all the color values to the top, then finding the distinct ranges of each of the 4 x color values and trying to color by a range rather than individual row case statement. Sadly I have no idea how to do so. Obviously I would then resort by the original file format following all columns having been colored.

Can anyone please give me some example code on how I would do this ? I have sort code that seems to work, I just cannot figure how to get start and end cell addresses for a block color statement.

Set objRange2 = objXLWs.Cells(2, iC)
objXLWs.UsedRange.Sort objRange2, xlAscending, , , , , , xlYes

Many thanks for any help, Fin.

[Evertjan.]

Post by Fin
Using Vbscript for an Excel 2010 worksheet, how can I find the start and
end range of distinct values with a column ?

Methinks that is VBA, this NG is about VBS.

[Fin]

No, I use a .vbs file external to Excel, run from a DOS window, inputting a CSV file as a variable, that gets saved as an Excel file following the processing as part of the script. It is not a VBA Macro.

[Mayayana]

| No, I use a .vbs file external to Excel, run from a DOS window, inputting
a CSV file as a variable, that gets saved as an Excel file following the
processing as part of the script. It is not a VBA Macro.
|

You may get an answer here, but you'd probably still
be better off in an Office or Excel group. People who
use VBS don't necessarily use MS Office. You might
be using VBS but the object model is unknown to anyone
not using Excel.

[Evertjan.]

Post by Mayayana

Post by Fin
No, I use a .vbs file external to Excel, run from a DOS window,
inputting a CSV file as a variable, that gets saved as an Excel file
following the processing as part of the script. It is not a VBA Macro.

You may get an answer here, but you'd probably still
be better off in an Office or Excel group. People who
use VBS don't necessarily use MS Office. You might
be using VBS but the object model is unknown to anyone
not using Excel.

However, CSV-file manipulation by vbs as a cscript or wscript executed
file or serverside under ASP is on topic in, and hopefully within the
expertise of, this NG.

Post by Mayayana

Post by Fin

Post by Fin
Using Vbscript for an Excel 2010 worksheet, how can I find the start
and end range of distinct values with a column ?

=================================

I often offer a CSV output view selected from a serverside database system
to let my users convert that to Excel and do their own statistical
manipulations, as they [wrongly, meseems] believe that that is the way to
go.

I use the ; as a delimiter then.

[Dave "Crash" Dummy]

Post by Evertjan.

Post by Fin
No, I use a .vbs file external to Excel, run from a DOS window,
inputting a CSV file as a variable, that gets saved as an Excel
file following the processing as part of the script. It is not a
VBA Macro.

You may get an answer here, but you'd probably still be better off
in an Office or Excel group. People who use VBS don't necessarily
use MS Office. You might be using VBS but the object model is
unknown to anyone not using Excel.

However, CSV-file manipulation by vbs as a cscript or wscript
executed file or serverside under ASP is on topic in, and hopefully
within the expertise of, this NG.

Post by Fin

Post by Fin
Using Vbscript for an Excel 2010 worksheet, how can I find the
start and end range of distinct values with a column ?

=================================
I often offer a CSV output view selected from a serverside database
system to let my users convert that to Excel and do their own
statistical manipulations, as they [wrongly, meseems] believe that
that is the way to go.
I use the ; as a delimiter then.

I have no problem with CSV, but ".Item(iR, iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code suggestions.
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

[Evertjan.]

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR, iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code suggestions.

Indeed.

Post by Dave "Crash" Dummy
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

That's why using sql database-functions are [so much] better.

However as an "Crash\" Dummy" <***@invalid.invalid>,
are you in a hurry?

[Dave "Crash" Dummy]

Post by Evertjan.

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR,
iC-1)).Interior.ColorIndex" is Greek to me, not VBS. I have no way
to test any code suggestions.

Indeed.

Post by Dave "Crash" Dummy
Anyway, manipulating a 200,000 x 45 array with VBS is going to be
slow no matter what you do.

That's why using sql database-functions are [so much] better.
hurry?

Not me. I'm retired. I have some scripts that can take 15 to 20 minutes
or even longer to run, depending on the complexity of the generated image.

[GS]

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR,
iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code suggestions.
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

Are you saying you don't have/use MS Office or Excel?

[Dave "Crash" Dummy]

Post by GS

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR, iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code suggestions.
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

Are you saying you don't have/use MS Office or Excel?

Yes.

[GS]

Post by GS

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR,
iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code
suggestions.
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

Are you saying you don't have/use MS Office or Excel?

Yes.

Ah! Well since OP didn't post entire code it's Greek to me too what the
vars are, but usage looks like row/column indexes to me.<g>

[Evertjan.]

Post by GS

Post by GS

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR,
iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code
suggestions.
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

Are you saying you don't have/use MS Office or Excel?

Yes.

Ah! Well since OP didn't post entire code it's Greek to me too what the
vars are, but usage looks like row/column indexes to me.<g>

They seem to be part of VBA, not of VBS.

[Mayayana]

| > Ah! Well since OP didn't post entire code it's Greek to me too what the
| > vars are, but usage looks like row/column indexes to me.<g>
|
| They seem to be part of VBA, not of VBS.
|

He already said it's a VBS file. Even if it weren't, it
certainly could be. That's not the problem. The problem
is that he's essentially using 3rd-party software. It's
dealing with MS Office object model. MS Office is not
part of Windows. Like Dave, I don't use MS Office and
never have. By contrast, something like IE object model
is common to any Windows machine, so it's relevant
to VBScripters. Unfortunately, the people who use
MS Office tend to live in a parochial world where they
assume that everyone else also uses MS Office.

[Dave "Crash" Dummy]

Post by Mayayana
| > Ah! Well since OP didn't post entire code it's Greek to me too what the
| > vars are, but usage looks like row/column indexes to me.<g>
|
| They seem to be part of VBA, not of VBS.
|
He already said it's a VBS file. Even if it weren't, it
certainly could be. That's not the problem. The problem
is that he's essentially using 3rd-party software. It's
dealing with MS Office object model. MS Office is not
part of Windows. Like Dave, I don't use MS Office and
never have. By contrast, something like IE object model
is common to any Windows machine, so it's relevant
to VBScripters. Unfortunately, the people who use
MS Office tend to live in a parochial world where they
assume that everyone else also uses MS Office.

On the rare occasions when I have to deal with a spreadsheet, I use
LibreOffice.

[GS]

Post by Mayayana

Post by Evertjan.

Post by GS
Ah! Well since OP didn't post entire code it's Greek to me too what
the vars are, but usage looks like row/column indexes to me.<g>

They seem to be part of VBA, not of VBS.

He already said it's a VBS file. Even if it weren't, it
certainly could be. That's not the problem. The problem
is that he's essentially using 3rd-party software. It's
dealing with MS Office object model. MS Office is not
part of Windows. Like Dave, I don't use MS Office and
never have. By contrast, something like IE object model
is common to any Windows machine, so it's relevant
to VBScripters. Unfortunately, the people who use
MS Office tend to live in a parochial world where they
assume that everyone else also uses MS Office.

I see lately, though, that a 'starter' version of MS Office comes
pre-installed on new machines running Windows. I first noticed this on
my daughter's Toshiba laptop running Vista. Same with every new Win7
machine I've seen since. In fact, this very (Win7 Pro) machine came
with a full version ?-day (I forget how many) free trial of MSO 2010.
(I removed it because I have my own copy and its software 'key' wasn't
recognized by the pre-installed version! No surprise there<g>!)

[Dave "Crash" Dummy]

Post by GS

Post by Evertjan.

Post by GS
Ah! Well since OP didn't post entire code it's Greek to me too
what the vars are, but usage looks like row/column indexes to
me.<g>

They seem to be part of VBA, not of VBS.

He already said it's a VBS file. Even if it weren't, it certainly
could be. That's not the problem. The problem is that he's
essentially using 3rd-party software. It's dealing with MS Office
object model. MS Office is not part of Windows. Like Dave, I don't
use MS Office and never have. By contrast, something like IE
object model is common to any Windows machine, so it's relevant to
VBScripters. Unfortunately, the people who use MS Office tend to
live in a parochial world where they assume that everyone else also
uses MS Office.

I see lately, though, that a 'starter' version of MS Office comes
pre-installed on new machines running Windows. I first noticed this
on my daughter's Toshiba laptop running Vista. Same with every new
Win7 machine I've seen since. In fact, this very (Win7 Pro) machine
came with a full version ?-day (I forget how many) free trial of MSO
2010. (I removed it because I have my own copy and its software
'key' wasn't recognized by the pre-installed version! No surprise
there<g>!)

Just like all the other crapware that comes in OEM machines, that may be
true on machines that come with Windows preinstalled, but it is not part
of the Windows installation. I don't see anything resembling MS Office
or Excel on my Windows 7 Ultimate x64 installation DVD. If someone will
show me the code needed to activate the "objXLWs" object, I'll be happy
to try it.

[GS]

Post by Dave "Crash" Dummy

Post by GS

Post by Evertjan.

Post by GS
Ah! Well since OP didn't post entire code it's Greek to me too
what the vars are, but usage looks like row/column indexes to
me.<g>

They seem to be part of VBA, not of VBS.

He already said it's a VBS file. Even if it weren't, it certainly
could be. That's not the problem. The problem is that he's
essentially using 3rd-party software. It's dealing with MS Office
object model. MS Office is not part of Windows. Like Dave, I don't
use MS Office and never have. By contrast, something like IE
object model is common to any Windows machine, so it's relevant to
VBScripters. Unfortunately, the people who use MS Office tend to
live in a parochial world where they assume that everyone else also
uses MS Office.

I see lately, though, that a 'starter' version of MS Office comes
pre-installed on new machines running Windows. I first noticed this
on my daughter's Toshiba laptop running Vista. Same with every new
Win7 machine I've seen since. In fact, this very (Win7 Pro) machine
came with a full version ?-day (I forget how many) free trial of MSO
2010. (I removed it because I have my own copy and its software
'key' wasn't recognized by the pre-installed version! No surprise
there<g>!)

Just like all the other crapware that comes in OEM machines, that may be
true on machines that come with Windows preinstalled, but it is not part
of the Windows installation. I don't see anything resembling MS Office
or Excel on my Windows 7 Ultimate x64 installation DVD. If someone will
show me the code needed to activate the "objXLWs" object, I'll be happy
to try it.

Correct! It is not part of Windows (I didn't suggest it was!), and it
usually does come on the OEM system discs.

Look at Todd's example code...

[Dave "Crash" Dummy]

Post by GS

Post by Dave "Crash" Dummy

Post by GS

Post by Mayayana

Post by Evertjan.

Post by GS
Ah! Well since OP didn't post entire code it's Greek to me
too what the vars are, but usage looks like row/column
indexes to me.<g>

They seem to be part of VBA, not of VBS.

He already said it's a VBS file. Even if it weren't, it
certainly could be. That's not the problem. The problem is that
he's essentially using 3rd-party software. It's dealing with MS
Office object model. MS Office is not part of Windows. Like
Dave, I don't use MS Office and never have. By contrast,
something like IE object model is common to any Windows
machine, so it's relevant to VBScripters. Unfortunately, the
people who use MS Office tend to live in a parochial world
where they assume that everyone else also uses MS Office.

I see lately, though, that a 'starter' version of MS Office comes
pre-installed on new machines running Windows. I first noticed
this on my daughter's Toshiba laptop running Vista. Same with
every new Win7 machine I've seen since. In fact, this very (Win7
Pro) machine came with a full version ?-day (I forget how many)
free trial of MSO 2010. (I removed it because I have my own copy
and its software 'key' wasn't recognized by the pre-installed
version! No surprise there<g>!)

Just like all the other crapware that comes in OEM machines, that
may be true on machines that come with Windows preinstalled, but it
is not part of the Windows installation. I don't see anything
resembling MS Office or Excel on my Windows 7 Ultimate x64
installation DVD. If someone will show me the code needed to
activate the "objXLWs" object, I'll be happy to try it.

Correct! It is not part of Windows (I didn't suggest it was!), and it
usually does come on the OEM system discs.

It is not on my academic release disk, and even if it was, I wouldn't
have installed it. It can't be assumed that it will be on every Windows
installation.

Post by GS
Look at Todd's example code...

Ah.
"Error: ActiveX component can't create object: 'Excel.Application' "

[GS]

Post by Dave "Crash" Dummy
It is not on my academic release disk, and even if it was, I wouldn't
have installed it. It can't be assumed that it will be on every Windows
installation.

I didn't say it was in every installation! For clarity, when you buy a
new computer MS Office is pre-installed (along with other crapware) by
the OEM *after* Windows is loaded onto the machine.

[Todd Vargo]

Post by Dave "Crash" Dummy
It is not on my academic release disk, and even if it was, I wouldn't
have installed it. It can't be assumed that it will be on every Windows
installation.

Post by GS
Look at Todd's example code...

Ah.
"Error: ActiveX component can't create object: 'Excel.Application' "

That is because you don't have Excel installed.

[Dave "Crash" Dummy]

Post by Todd Vargo

Post by Dave "Crash" Dummy
It is not on my academic release disk, and even if it was, I wouldn't
have installed it. It can't be assumed that it will be on every Windows
installation.

Post by GS
Look at Todd's example code...

Ah.
"Error: ActiveX component can't create object: 'Excel.Application' "

That is because you don't have Excel installed.

Yes. I know. That is my point.

[GS]

Post by Evertjan.

Post by GS

Post by GS

Post by Dave "Crash" Dummy
I have no problem with CSV, but ".Item(iR,
iC-1)).Interior.ColorIndex"
is Greek to me, not VBS. I have no way to test any code
suggestions.
Anyway, manipulating a 200,000 x 45 array with VBS is going to be slow
no matter what you do.

Are you saying you don't have/use MS Office or Excel?

Yes.

Ah! Well since OP didn't post entire code it's Greek to me too what
the vars are, but usage looks like row/column indexes to me.<g>

They seem to be part of VBA, not of VBS.

It actually is vbs NOT vba. He's using automation and so the object ref
to Excel as "objXLApp"!<g>

[Evertjan.]

Post by GS

Post by Evertjan.
They seem to be part of VBA, not of VBS.

It actually is vbs NOT vba. He's using automation and so the object ref
to Excel as "objXLApp"!<g>

I cann't even pronounce that, so what is the object of this?

Does this object exist on windows servers?

[GS]

Post by Evertjan.

Post by GS

Post by Evertjan.
They seem to be part of VBA, not of VBS.

It actually is vbs NOT vba. He's using automation and so the object
ref to Excel as "objXLApp"!<g>

I cann't even pronounce that, so what is the object of this?
Does this object exist on windows servers?

Given that it's automation, it was created by...

Set objXLApp = CreateObject("Excel.Application")

[Todd Vargo]

Post by GS

Post by Evertjan.

Post by GS

Post by Evertjan.
They seem to be part of VBA, not of VBS.

It actually is vbs NOT vba. He's using automation and so the object
ref to Excel as "objXLApp"!<g>

I cann't even pronounce that, so what is the object of this?
Does this object exist on windows servers?

Given that it's automation, it was created by...
Set objXLApp = CreateObject("Excel.Application")

objXLApp is just a variable name. VBScript variables are of type
variant. So in this case, it is set as an Excel application object.

Here is a breakdown for this variable naming convention.

obj=Helps you remember the variable is an object
XL=The name of the application providing the object is Excel
App=The type or class of the object to is an application

Hence, objXLApp.

Note, OP's code had objXLWs so I just kept using it because I figured
they might have other code based on it which was not posted.

[GS]

Post by Todd Vargo

Post by GS

Post by Evertjan.

Post by GS

Post by Evertjan.
They seem to be part of VBA, not of VBS.

It actually is vbs NOT vba. He's using automation and so the object
ref to Excel as "objXLApp"!<g>

I cann't even pronounce that, so what is the object of this?
Does this object exist on windows servers?

Given that it's automation, it was created by...
Set objXLApp = CreateObject("Excel.Application")

objXLApp is just a variable name. VBScript variables are of type
variant. So in this case, it is set as an Excel application object.
Here is a breakdown for this variable naming convention.
obj=Helps you remember the variable is an object
XL=The name of the application providing the object is Excel
App=The type or class of the object to is an application
Hence, objXLApp.

Thanks for the detail! I'm not sure where I saw objXLApp in this post
but I didn't just make it up, and I know VBS vars are variant type.

Also, I'm well familiar with code naming conventions. My naming
convention for automating Excel from VB6 (for example) is...

Dim appXL
Set appXL = CreateObject("Excel.Application")

..which lets me know the variable refs an 'application object' by its
'app' Type prefix, and which application it refs by what follows the
type prefix.

Post by Todd Vargo
Note, OP's code had objXLWs so I just kept using it because I figured
they might have other code based on it which was not posted.

I would usually declare the var for the worksheet like so...

Dim wkbSource, wksSource
Set wkbSource = appXL.ActiveWorkbook
Set wksSource = wkbSource.Sheets("Data")

..where the type prefix 'wkb' tells me it's a 'workbook object', and
the prefix 'wks' tells me it's a 'worksheet object'. Thus, I do not
require the overkill of 'obj' prepended to my 'object naming'
prefixes.<g>

I use the same naming convention for VB, VBA, VBS, PowerBasic, and C++.
I'll likely persist this convention if I decide to use other languages
later on because it just doesn't make sense to change it! I even use
'appXL' in VBA projects because most my code is then 'portable' to the
other languages without breaking object ref convention, and so no
editing required to reuse it elsewhere.

It's pretty easy to figure out someone else's naming convention (if
they have one) by their code's usage consistency. If not consistent
then I assume the user has no convention at all and just uses code
obtained from here-n-there. (I see lots of that happening!<g>)

I'm extremely fortunate having gotten really good mentoring from
seasoned developers in my early years of programming. I now consider
myself a 'well disciplined' student of programming and application
development. It's my intent to persist the 'student' staus regardless
of what level of experience/expertise I achieve in any programming
language! (I'm in my 22nd year with Lou Gehrig's and so the 'schooling'
keeps the 'grey matter' thriving & healthy<g>)

[Todd Vargo]

Post by Fin
Using Vbscript for an Excel 2010 worksheet, how can I find the start and end range of distinct values with a column ?
I have a worksheet with some 200k rows, and 45 columns. 20 of such columns contain rows indicating a color value that I need to set. I have code that works but when working on 200k rows it is incredibly slow.
Set objRange = objXLWs.UsedRange
iRows = objRange.Rows.Count
iColumns = objRange.Columns.Count
For iR = 2 To iRows
Select Case objRange.Item(iR, iC).Value
Case "Y"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 6 'yellow
Case "R"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 3 'red
Case "O"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 45 'orange
Case "T"
objXLApp.Range(objRange.Item(iR, iC-1),objRange.Item(iR, iC-1)).Interior.ColorIndex = 42 'terquoise
End Select
Next
What I am thinking is that sorting the column, bringing all the color values to the top, then finding the distinct ranges of each of the 4 x color values and trying to color by a range rather than individual row case statement. Sadly I have no idea how to do so. Obviously I would then resort by the original file format following all columns having been colored.
Can anyone please give me some example code on how I would do this ? I have sort code that seems to work, I just cannot figure how to get start and end cell addresses for a block color statement.
Set objRange2 = objXLWs.Cells(2, iC)
objXLWs.UsedRange.Sort objRange2, xlAscending, , , , , , xlYes
Many thanks for any help, Fin.

It helps if you post a complete working example for others to build on.
You are using vbscript but what is posted lacks a connection to an Excel
object. The code is also missing the iC loop.

i.e. something like this...'
Set objXLWs = CreateObject("Excel.Application")

Unfortunately, because the code visits every cell to read its value then
checking if it meets a color criteria and then finally change the color
of the cell to its left, well, yes, that is going to take some time.

This code seems to work slightly faster for me. Sorting did not seem to
make any difference in time. Making a different sheet active before the
loop begins seemed slightly faster (I did not time it). Reading and
testing the values takes no time at all. Its changing the colors that
takes up most of the time.

Try this and let us know how it goes compared to your way.

Set objRange = objXLWs.UsedRange
iRows = objRange.Rows.Count
iCols = objRange.Columns.Count

For iR = 1 To iRows
For iC = 2 To iCols
Select Case objRange(iR, iC).Value
Case "Y"
objRange(iR, iC - 1).Interior.ColorIndex = 6 'yellow
Case "R"
objRange(iR, iC - 1).Interior.ColorIndex = 3 'red
Case "O"
objRange(iR, iC - 1).Interior.ColorIndex = 45 'orange
Case "T"
objRange(iR, iC - 1).Interior.ColorIndex = 42 'turquoise
End Select
Next
Next