Background Color Rows in a Named Range ... 1

[roadstain]

I have a large spreadsheet of information, about 3000 lines and growing. The entire "database" is a named range. I have a second named range for a column within the database named range. What I need to do is "gray shade" those rows within the named range when the column range is equal to a value given in an entry form. What is the quickest way to do this?

I must be missing something obvious because the routine I have now takes forever to run.

 

[DaleWatson123321]

Hi qritch,

If you want the QUICKEST method, please allow me to demonstrate the EXTREME POWER of Excel's "database functionality". This includes formulas, but ALSO the ability to extract out and isolate the "specific" data that meets the "criteria" your user specifies in your entry form.

Most Excel users are "scared off" from utilizing this EXTREMELY POWERFUL component, because of a couple of BUGS that Microsoft continues to ignore. However these BUGS are "harmess" in the sense that users can "also ignore" them AFTER they learn that they involve "FALSE" ERROR MESSAGES. These BUGS say: 1) you cannot extract data to a SEPARATE sheet, and 2) you cannot place your "criteria" on a SEPARATE sheet. Both of these are indeed POSSIBLE.

If I managed to catch your interest, I would recommend that the easiest, fastest, and most practical method of learning this, is for you to email me a "scaled down" version of your file. Just include the field names, and a small number of records, and a description of what type of data you want to isolate.

I'll then insert the necessary code and return the file.

Naturally if you have sensitive data, simply replace it with fictitious data - preferably with data that still reflects that type of data you're working with.

Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca

 

[xlbo]

Try summat like:
range("DatabaseRangeName&quot.interior.colorindex = xlnone
For Each cell in range("ColumnRangeName&quot
If cell.value = frmname.textboxname.value then
mRow = cell.row
range("A" & mRow & ":Z" & _ mRow).interior.colorindex = 15
else
end if
next

Dale - could you send me some sample code for extraction using your "world famous" database functionality - it's one thing I've never coded (mainly 'cos of that error message as you say) and I'm intrigued as to how it works
Rgds
Geoff HTH
~Geoff~

 

[roadstain]

Thanks - found what I was doing wrong. Geoff's reponse was what I was trying to set up for the most part.

 

[DaleWatson123321]

Hi guys,

qrich,

I feel "let down" that you didn't take me up on my offer.

Just to "reinforce" the point I was attempting to make, there was a posting a couple of months ago - where a contributor recommend a typical "looping" method not unlike the one you are using to loop through the records.

In testing that proposed solution, I gave it a "full test" - i.e. on about 65,000 records. After waiting for over 37 MINUTES, I gave up, and broke out of the routine.

By comparison, my routine, that utilizes what I've been referring to as Excel's POWERFUL "database functionality" - which I notice Geoff referenced as 'your "world famous" database functionality' - took EXACTLY "2 SECONDS" to perform EXACTLY the same function. Yes, that's 37+ MINUTES, compared to just 2 SECONDS.

For others to whom I've sent example files of these database functions, and still others for whom I've created "specific" solutions, some simple and some very "dynamic", they are indeed EXTREMELY IMPRESSED with this (most unutilized) "RAW POWER".

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

Geoff,

"Finally" ... I've been waiting for "that day" - when some of the "traditional" programmers begin to ask for this information - to see "what all the hype is about", and indeed whether this is such a GOOD thing.

Other programmers have asked for and received my example files and specific solutions. However, I believe you have the distinction of being the first of the "regulars" at Tek-Tips to ask for this information.

Naturally, I'm only too pleased to send you some of these example files. Unfortunately, these files are relatively simple, but should still serve to demonstrate this untapped component of Excel. I'd love to be able to send a couple of the "dynamic" files, but unfortunately, I can't because the files are ones that were sent to me in confidence.

I believe one of those types of files, for example, was one I did for "funperro". If you recall, you had asked me (and him) to consider Pivot Tables as an option. Neither of us did. He was QUITE pleased with the file I sent him, and when I last checked with him, he still had not looked into the use of Pivot Tables. Perhaps I could check with "funperro" to see if he would be in agreement to sharing his file with you. It would be "good" one for you to use in comparing against the use of Pivot Tables.

I would hasten to add, that if you or anyone runs into any questions, hurdles or roadblocks... please don't hesitate to ask.

I'll get some of these files off to you shortly.

Best Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca

 

[roadstain]

Oh I will, just need to get some quick code out the door to get this done. You see I am using a user form for the user to enter the value. In doing so I can evaluate the entry to ensure no one does something stupid.

 

[segmentationfault]

I too was intrigued by this question and did some algorithm analysis. I tried three algorithms on 10,000 rows of data with 6 different keys.

1. Iteration through each cell in the column
2. Using Find to skip through each occurrence of the pattern

Both of these had almost identical run times.

3. I inserted another column that echoed the row number, called "RowNum" for clarity. I then selected all the data, sorted it by the key column, used Find to locate the top of the rows that matched, used a function I wrote to find the bottom of the rows that matched, shaded them, and then resorted according to the "RowNum" column to put everything back in order. This was over 90% faster than the first two methods, much to my total amazement.

I did not use "for each cells in range", but I'll examine that one next.

 

[segmentationfault]

I just finished the "for each cells in range" approach, and it was about 60% faster than iterating through each of the cells, 60% faster than looping with find, and 36% slower than sorting the data and highlighting an entire block at once.

Also, someone should mention that the macro will speed up dramatically if you do Application.ScreenUpdating = false at the beginning and Application.ScreenUpdating = true at the end of the macro

 

[segmentationfault]

I realized that I posted those times in a meaningless fashion. Here's another try.

1.0 - Sorting the data, shading a block, resorting it.
4.5x - For each cells in range
11.5x - calling Find repeatedly
11.5x - iterating through each cell

That should be pretty self explanatory, I think...

 

[DaleWatson123321]

Hi all...

'Tis good to see others "getting into the action" !!!

segmentationfault...

If I were to email you the file I spoke of, would you be willing to "apply your programming skills" with this particular file - to see if you can beat the 37+ MINUTES ???

...and then report your results on this thread ?

...and would you ALSO be prepared to comment on the "validity" of my routine doing EXACTLY the same in "2 SECONDS ?

It might make for "some interesting reading" for the other "Tek-Tippers".

Best Regards, ...Dale Watson

WORK: dwatson@bsi.gov.mb.ca
HOME: nd.watson@shaw.ca

 

[segmentationfault]

Zounds, you went from 37 minutes to 2 seconds? Not bad... I went from over 4 seconds to .2 seconds through my different methods, so you've got me beat.

I'll send you an email tomorrow from the address to which I'd like you to send the file.. I don't know it off hand.

I'm an algorithm guy at heart, so I'd be happy to take a look. For a job offer in the Virginia area, I'll even do a colorful PowerPoint presentation. Just joking. Sorta.

 

[xlbo]

Dale - my email address is
Geoff.Barraclough@PunchPubs.co.uk
If you could send a sample I'd appreciate it - simple is fine - I'd like to see how I can build it into any of my "apps"
ps - Dale - I wouldn't claim to be a programmer - that would be an insult to the *true* programmers out there.I can program excel cos I know what the functionality is, not vice versa but I guess it don't make much difference in teh long run. I still tend to do a lot of stuff via formulae and sheet based work and just really use code to automate - hence my interest in your methods
I'll also look at beating 37 mins to do the loop

HTH
~Geoff~

 

[segmentationfault]

Alright, I have examined the solutions in this workbook. (It must be feeling like a cheap date by now. It's been passed around and we're all sharing stories about how fast it is.)

The macro I have written ("twice-sorted", for conversational purposes) strictly meets the specifications put forth in the original post. Input a value in an InputBox, and all rows that match that input in a predetermined column get shaded.

That said, I can shade every row that has an x in col1 in 2.9 seconds. All 41,000 or so of them. All rows that contain "name1" in col1 takes 2.3 seconds. Those times are averaged over 20 runs.

In light of that, the iterative methods are hardly worth examining. My previous study had runtimes of 2300 ms for 10,000 rows of data, which is comparable to the twice-sorted method with 65,000 rows.

The database functions written by Dale are certainly very powerful and provide more functionality than what I've written. That said, I still think I can shade 41,000 rows faster than those database functions but we're pushing the envelope of pragmatism.

The database functions do more for less code, and really what does a 2 second difference mean on 65,000 records? The trade-off is that I can write a macro to do everything the database function does and more, but let's not skin a cat with a chainsaw.

For public interest, I'm going to clean up and post the routine I have to time algorithms. I've used it to conclude that

 If Not (name) Is Nothing Then

is 4% faster than

 If (name) Is Nothing Then
Else
(actual instructions)

as well as analyze the algorithms we've talked about here.

In conclusion, if those database functions do what you want, then I would take them and run. If you want more customizable control without any sacrifice in speed, an optimized macro will fit the bill.

 

[segmentationfault]

This code comes with the best warranty you can get for the money. Enjoy.

Option Explicit

Private Declare Sub GetLocalTime Lib "kernel32" _
    (lpSystem As SYSTEMTIME)
    
Private Type SYSTEMTIME
      wYear          As Integer
      wMonth         As Integer
      wDayOfWeek     As Integer
      wDay           As Integer
      wHour          As Integer
      wMinute        As Integer
      wSecond        As Integer
      wMilliseconds  As Integer
End Type

Private sysStartTime As SYSTEMTIME
Private sysEndTime As SYSTEMTIME

' This constant represents the number
' of runs that will be averaged.  It's
' also 1 low, as in n=10 makes 11 runs.
Private Const n As Integer = 10

' ***********************************************
' This subroutine will calculate the average time
' in milliseconds necessary to run the supplied
' subroutine.
'
' The validity of these results hinges on two
' things:
' 1. You do not adjust the code between the calls
' to GetLocalTime other than to insert the name
' and parameters for your subroutine.
' 2. Your routine does not take more than an hour
' to run once.  But come on, if you're actually
' analyzing such a monstrosity with this VBA
' macro, you have bigger things to worry about.

Sub Timer()
    Dim j As Integer

    ' The starting timer values
    Dim sHr As Integer
    Dim sMin As Integer
    Dim sSec As Integer
    Dim sMSec As Integer
    
    ' The ending timer values
    Dim eHr As Integer
    Dim eMin As Integer
    Dim eSec As Integer
    Dim eMSec As Integer
    
    ' Eliminate delay due to
    ' drawing video
    Application.ScreenUpdating = False
    Dim Times(n) As Double
    
    ' Use this block to get input from the user
    ' Do not accept input inside the For loop.
    ' Dim s As String
    ' s = InputBox("At whose memory segment shall I peek?", "SIGSEV")
    
    For j = 0 To n
        ' ************************
        '
        ' Get starting time
        GetLocalTime sysStartTime
        
        ' Run the algorithm
        Call SegmentationFault
        
        ' Record finish time
        GetLocalTime sysEndTime
        ' ************************
        
        ' Calculate elapsed time
        sMin = sysStartTime.wMinute
        sSec = sysStartTime.wSecond
        sMSec = sysStartTime.wMilliseconds
        eMin = sysEndTime.wMinute
        eSec = sysEndTime.wSecond
        eMSec = sysEndTime.wMilliseconds
        If eMSec < sMSec Then
            If eSec > 0 Then
                eSec = eSec - 1
                eMSec = eMSec + 1000
            ElseIf eMin > 0 Then
                eMin = eMin - 1
                eSec = eSec + 59
                eMSec = eMSec + 1000
            Else
                eMin = 59
                eSec = eSec + 59
                eMSec = eMSec + 1000
            End If
        End If
        If eSec < sSec Then
            If eMin > 0 Then
                eMin = eMin - 1
                eSec = eSec + 60
            Else
                eMin = 59
                eSec = eSec + 60
            End If
        End If
        If eMin < sMin Then
            eMin = eMin + 60
        End If

        Times(j) = (eMSec - sMSec) + 1000 * (eSec - sSec) + 60000 * (eMin - sMin)

        Undo
    Next j
    
    ' Accumulate the run times in Times(0)
    For j = 1 To n
        Times(0) = Times(0) + Times(j)
    Next j
    
    ' Display the average
    MsgBox &quot;Average time: &quot; + Trim(Str(Times(0) / 10)), vbOKOnly
    Application.ScreenUpdating = True
End Sub

Sub SegmentationFault()
    ' Count from 1 to 1,000,000, fool!
    Dim i As Long
    For i = 1 To 1000000
    Next i
End Sub

 

[segmentationfault]

Should have included another comment toward the end:

   ' Stick a method here to clean up your
   ' mess for the next run of the algorithm,
   ' if necessary
   Undo

 

[roadstain]

Well this turned into something.

Dale, my email is qritch@hotmail.com

I ended up doing what I was going on about above. I have a user form which asks the user to input the item that is getting Gray Shaded. So of course the first thing this does is find if there are any to report a no find error. If so, it allows from a check box to use today's date (system date) to auto update a field in the spreadsheet. From there the entries matching what the user entered are gray shaded.

As for the rest of the conversation, amazing.