Issue with blanks and spaces

Discussion in 'Excel Programming' started by L. Howard, Mar 16, 2014.

  1. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 24 Mar 2014 09:43:56 -0700 (PDT) schrieb L. Howard:

    > I have studied the code for Titles and Descriptions but I cannot figure out what to change in the Description code to get 2 second performance like Titles code does.


    have another look for the workbook.


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 24, 2014
    #41
    1. Advertisements

  2. L. Howard

    L. Howard Guest


    > have another look for the workbook.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    Well, that certainly looks like a winner.

    Code run time and duplicates are very workable.

    I don't know you do it!!

    A ton of thanks.

    Howard
     
    L. Howard, Mar 24, 2014
    #42
    1. Advertisements

  3. L. Howard

    L. Howard Guest

    On Monday, March 24, 2014 12:33:42 PM UTC-7, L. Howard wrote:
    > > have another look for the workbook.

    >
    > >

    >
    > >

    >
    > >

    >
    > >

    >
    > >

    >
    > > Regards

    >
    > >

    >
    > > Claus B.

    >
    > >

    >
    > > --

    >
    >
    >
    > Well, that certainly looks like a winner.
    >
    >
    >
    > Code run time and duplicates are very workable.
    >
    >
    >
    > I don't know you do it!!
    >
    >
    >
    > A ton of thanks.
    >
    >
    >
    > Howard


    Two bugs have popped up

    The first is with the Description Builder sheet out put to column A.
    Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row.

    Here are a few easier to fine examples.

    In A2 in the formula bar arrow to the right until you find "...select Matteresses.Most of our...".

    Should be a space between "...Matteresses. Most of our..."

    In A14 find arrow over to "...outlets.Much of our..."
    In A22 "...completion.A huge selection..."

    I cannot find in the data where or what would make that happen or with the code, whichever one it is that fails to put the space in.

    The other glitch has occurred with the code for CL PVA's sheet where it moves the top row to the bottom and then shifts all upward.

    TopRow = Rows(FirstRow) this line of code errors out.

    Here is a link if needed and you have the time to take a look.

    https://www.dropbox.com/s/ebd1f1ao4ul3pnn/Client Data Work Book _1 rev 3.1 Drop Box.xlsm

    Thanks.
    Howard
     
    L. Howard, Mar 28, 2014
    #43
  4. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard:


    > The first is with the Description Builder sheet out put to column A.
    > Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row.


    I changed the code and RTrim the values

    > TopRow = Rows(FirstRow) this line of code errors out.


    have a look:
    https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
    for the workbook "Client Data Work Book _1 rev 3.1_C.xlsm"
    In Module2 is the code "Move". It is easier and shorter.


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Mar 28, 2014
    #44
  5. L. Howard

    L. Howard Guest

    On Friday, March 28, 2014 1:24:32 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Thu, 27 Mar 2014 18:58:23 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    >
    >
    > > The first is with the Description Builder sheet out put to column A.

    >
    > > Each entry is made up of 5 or 6 short phrases and I have noticed the there is no space between the second and third phrase. Seems consistent in each row.

    >
    >
    >
    > I changed the code and RTrim the values
    >
    >
    >
    > > TopRow = Rows(FirstRow) this line of code errors out.

    >
    >
    >
    > have a look:
    >
    > https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
    >
    > for the workbook "Client Data Work Book _1 rev 3.1_C.xlsm"
    >
    > In Module2 is the code "Move". It is easier and shorter.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.



    The Description Builder is looking real fine.

    The Move code in Module 2 errors out on this line:

    Rows("3:" & LRow).Cut

    The code makes no mention to a sheet name, should I incorporate it in the code in Module 1? When the line errors out and you mouse over the line it refers to row 21 which looks correct on the CL PVA's sheet as it is the last row.

    Scratching my head on this.

    Howard
     
    L. Howard, Mar 28, 2014
    #45
  6. L. Howard

    L. Howard Guest

    Whoa! Hold everything! I saved and closed the workbook and then reopened it and everything seems to be working fine.

    Before I had a couple errors both in Titles code, clear line. and Description, clear line.

    The Move code was erring before, but now it seems to be just fine. Don't know what was going on.

    I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.

    It might just be the late hour here clouding my mind...

    Howard
     
    L. Howard, Mar 28, 2014
    #46
  7. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:

    > I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.


    sorry, my bad. I didn't refer correctly.
    Change the "Move" code to:

    Sub Move()
    Dim LRow As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("CL PVA's")
    LRow = .Cells(Rows.Count, 1).End(xlUp).Row
    .Rows("3:" & LRow).Cut
    .Rows("2:" & LRow - 1).Insert Shift:=xlDown
    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End Sub


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Mar 28, 2014
    #47
  8. L. Howard

    Claus Busch Guest

    Hi again,

    Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:

    > I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.


    if the macro is bound to the button on the sheet the sheet must be the
    active sheet to press the button. And code without refering always works
    on the active sheet.


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Mar 28, 2014
    #48
  9. L. Howard

    L. Howard Guest

    On Friday, March 28, 2014 3:10:44 AM UTC-7, Claus Busch wrote:
    > Hi again,
    >
    >
    >
    > Am Fri, 28 Mar 2014 02:53:17 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > I am still puzzled how the Move code in Module 2 knows to do it job on CL PVA's sheet.

    >
    >
    >
    > if the macro is bound to the button on the sheet the sheet must be the
    >
    > active sheet to press the button. And code without refering always works
    >
    > on the active sheet.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --
    >
    > Vista Ultimate SP2 / Windows7 SP1
    >
    > Office 2007 Ultimate SP3 / 2010 Professional


    Okay, Got it. Sure do thank you for the little clean ups of the code.

    Howard
     
    L. Howard, Mar 28, 2014
    #49
  10. L. Howard

    L. Howard Guest

    Hi Claus,

    A revisit to Description Builder if and when you have time please.

    First, all the code work you have offered works fine and does what was expected.

    The problem I'm looking at now may not have a practical fix.

    Duplicate returns were an issue to begin with, but after some usage that seems to be a non issue now.

    What is at issue is "identical like phrase elements" in close proximity to each other in the Column A list.

    The link should open to "Description Builder" sheet and that is the only sheet at issue.

    To illustrate what the problem is copy any one of the phrase elements in the orange range, and do a "Find All" on column A. With the box expanded youcan see the cells that hold the chosen phrase element. Usually there are around 95 to 130 cells listed.

    The problem is that they are often only 15 to 30 rows apart.

    Crazy as it may seem, that causes a pretty serious problem/penalty further down the road with the use of the long 5 and 6 phrase row entries.

    So the question is from a practical stand point, can the code or the on sheet methods, be change to broaden the proximity of these phrase elements?

    I'm calling a 'phrase element' any of the text in the orange and other colored ranges like it. And these are the ones that wind up too close to each other.

    The long phrases are the five and six short phrases combined in a column A row entry.

    So if you would copy and find all for the entry of D6 "Come Visit Us!" thatwould be what I'm talking about. The first two cells with "Come Visit Us!" are in A13 and A26. That's too close.


    https://www.dropbox.com/s/9x9ma0o2yz5ee2b/Location Builder rev 1.0 DB.xlsm

    Thanks.
    Howard
     
    L. Howard, Apr 9, 2014
    #50
  11. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 03:43:16 -0700 (PDT) schrieb L. Howard:

    > A revisit to Description Builder if and when you have time please.


    can you further explain what should be done in this sheet?
    You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in
    D:N) and these phrases should be copied in AE:AO. So each column should
    only have 20 phrases. But in the header is written 200 and in the column
    are 2000.
    So what is right? And out of 120 phrases you can't make 2000 values in
    column A.


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 9, 2014
    #51
  12. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 13:27:35 +0200 schrieb Claus Busch:

    > can you further explain what should be done in this sheet?
    > You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in
    > D:N) and these phrases should be copied in AE:AO. So each column should
    > only have 20 phrases. But in the header is written 200 and in the column
    > are 2000.


    in one loop you only get 20 phrases in columns AE:AO. So you have to
    loop 100 times and get some duplicates.
    What about the time the macro needs for running? You could do more
    loops, write it all in one column and then create with
    Scripting.Dictionary unique values. I will check this later when I have
    time. But I don't know if that will reduce the duplicates. So you loop
    100 times every value from D:N will occure 100 times. With more loops
    and creating unique values the occurence will not be reduced but there
    will be no same complete phrases.


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 9, 2014
    #52
  13. L. Howard

    L. Howard Guest


    > can you further explain what should be done in this sheet?
    >
    > You build in AQ out of 720 phrases 120 (6x20 if all cells are filled in
    >
    > D:N) and these phrases should be copied in AE:AO. So each column should
    >
    > only have 20 phrases. But in the header is written 200 and in the column
    >
    > are 2000.
    >
    > So what is right? And out of 120 phrases you can't make 2000 values in
    >
    > column A.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >


    The Headers are incorrect, I should have cleaned that up before sending. Sorry, that is bad info.

    Howard
     
    L. Howard, Apr 9, 2014
    #53
  14. L. Howard

    L. Howard Guest

    > in one loop you only get 20 phrases in columns AE:AO. So you have to
    >
    > loop 100 times and get some duplicates.
    >
    > What about the time the macro needs for running? You could do more
    >
    > loops, write it all in one column and then create with
    >
    > Scripting.Dictionary unique values. I will check this later when I have
    >
    > time. But I don't know if that will reduce the duplicates. So you loop
    >
    > 100 times every value from D:N will occure 100 times. With more loops
    >
    > and creating unique values the occurence will not be reduced but there
    >
    > will be no same complete phrases.
    >
    > Regards
    >
    > Claus B.
    >


    The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A.

    Not sure if it is relevant, but the problem does not happen on the "Titles Builder" sheet where it uses the same data to generate 12,000 rows. But Titles only uses a single phrase per row in its column A.

    The code to transfer phrases to column A have some differences of course, transferring one single phrase for Titles vs. five or six for Descriptions, but I was wondering if that could be part of the solution.

    The Scripting Dictionary would be worth trying.

    If that or any other ideas fall short then that may just have to be the brutal reality of it all.

    What you've done so far is pretty darned remarkable to me, and trying to hit a moving target with code has to have its limits.

    Howard
     
    L. Howard, Apr 9, 2014
    #54
  15. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard:

    > The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A.


    there is no chance to fit this behaviour. The dictance between those
    phrases should be greater if all colored fields in D:N were filled.
    Now you have only 220 phrases to create the descriptions. If all fields
    are filled you would have 720. Four colors have no values and yellow has
    only 100 instead of 120


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 9, 2014
    #55
  16. L. Howard

    L. Howard Guest

    On Wednesday, April 9, 2014 7:07:07 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Wed, 9 Apr 2014 06:30:18 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > The main issue is the occurrence of those "text elements" in the orange (and other colored ranges like the orange one) in rows too close together in column A.

    >
    >
    >
    > there is no chance to fit this behaviour. The dictance between those
    >
    > phrases should be greater if all colored fields in D:N were filled.
    >
    > Now you have only 220 phrases to create the descriptions. If all fields
    >
    > are filled you would have 720. Four colors have no values and yellow has
    >
    > only 100 instead of 120
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >


    Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point.

    Regards,
    Howard
     
    L. Howard, Apr 9, 2014
    #56
  17. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 9 Apr 2014 07:33:18 -0700 (PDT) schrieb L. Howard:

    > Okay, thanks Claus. Appreciate you taking a look and all you have done to get the project to this point.


    to copy the values to A try following macro instead of
    CopyToA2_2_Descript:

    Sub CopyToDiscript()

    Dim i As Long, j As Long
    Dim myArr As Variant

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With Sheets("Description Builder")

    For i = 2 To 1902 Step 100
    For j = 31 To 41 Step 2
    If WorksheetFunction.CountA(.Range(.Cells(i, j), _
    .Cells(i + 99, j))) = 100 Then
    myArr = .Range(.Cells(i, j), .Cells(i + 99, j))
    .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) _
    .Resize(100) = myArr
    End If
    Next
    Next
    myArr = .Range("A2:A2001")
    End With

    Sheets("Publish Data").Range("E2").Resize(rowsize:=UBound(myArr)) =
    myArr

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    End Sub


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 9, 2014
    #57
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Andy
    Replies:
    1
    Views:
    162
    shockley
    Sep 23, 2003
  2. GJones

    Delete rows with text and blanks in column A

    GJones, Nov 19, 2003, in forum: Excel Programming
    Replies:
    0
    Views:
    81
    GJones
    Nov 19, 2003
  3. mary
    Replies:
    3
    Views:
    114
    Tom Ogilvy
    Jan 19, 2004
  4. jrb

    Deleting blanks and shifting left - cont.

    jrb, Feb 27, 2004, in forum: Excel Programming
    Replies:
    3
    Views:
    166
    Tom Ogilvy
    Feb 27, 2004
  5. justaguyfromky

    copy range of cells with blanks then paste without blanks

    justaguyfromky, Sep 3, 2006, in forum: Excel Programming
    Replies:
    5
    Views:
    178
    justaguyfromky
    Sep 3, 2006
  6. Ben
    Replies:
    1
    Views:
    168
    Pflugs
    Jul 10, 2007
  7. robs3131

    Issue removing leading and lagging spaces

    robs3131, Jan 24, 2008, in forum: Excel Programming
    Replies:
    11
    Views:
    122
    Dave Peterson
    Feb 14, 2008
  8. Rover

    Blanks and Non blanks

    Rover, Apr 8, 2008, in forum: Excel Programming
    Replies:
    5
    Views:
    275
Loading...