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 Thu, 20 Mar 2014 06:14:42 -0700 (PDT) schrieb L. Howard:

    > Sheets("Sheet2").Range("B2").Resize(lRowCount - 1) = myArr


    that is my bad

    > I did notice that Column AE2 is blank and the blank repeats every 20 rows. Does not affect the copy that your code does. All the blank rows are copied as 5 short phrases instead of 6. More troubleshooting to do.


    do you want to skip blank cells?
    Can you send me the workbook?


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

  2. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Thu, 20 Mar 2014 06:14:42 -0700 (PDT) schrieb L. Howard:

    > I did notice that Column AE2 is blank and the blank repeats every 20 rows. Does not affect the copy that your code does. All the blank rows are copied as 5 short phrases instead of 6. More troubleshooting to do.


    is following code that what you want?

    Sub CopyToA2()
    Dim i As Long, j As Long
    Dim myStr As String
    Dim myArr As Variant

    Application.ScreenUpdating = False
    For i = 2 To 2001
    myStr = ""
    For j = 31 To 41 Step 2
    If Len(Replace(Cells(i, j), " ", "")) > 0 Then
    myStr = myStr & Cells(i, j)
    End If
    Next
    Cells(i, 1) = myStr
    Next
    myArr = Range("A2:A2001")
    Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
    Application.ScreenUpdating = True
    End Sub


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

  3. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Thu, 20 Mar 2014 15:00:24 +0100 schrieb Claus Busch:

    > is following code that what you want?


    if the result is what you want try following code. It is a bit faster.
    If not please send me the workbook with the expected result.

    Sub CopyToA2_2()
    Dim i As Long, j As Long
    Dim myStr As String
    Dim myArr As Variant

    Application.ScreenUpdating = False
    myArr = Range("AE2:AO2001")
    For i = LBound(myArr) To UBound(myArr)
    myStr = ""
    For j = 1 To 11 Step 2
    If Len(Replace(myArr(i, j), " ", "")) > 0 Then
    myStr = myStr & myArr(i, j)
    End If
    Next
    Cells(i + 1, 1) = myStr
    Next
    myArr = Range("A2:A2001")
    Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
    Application.ScreenUpdating = True
    End Sub


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 20, 2014
    #23
  4. L. Howard

    L. Howard Guest

    Hi Claus,

    https://www.dropbox.com/s/w7swpuj776m8w0y/Copy of Title Builder Randomizer rev 2.2 Drop Box.xlsm

    I think we are very close.

    The sheets of concern at this time are Publish Data, Title Builder and Description Builder.

    Title code needs to fill column A of Title sheet and column B of Publish.

    Description code needs to fill column A of Description sheet and column E of publish.

    Mostly it is doing that but the list seem to be correctly done but not identical.

    That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.

    The same for Description.

    Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.

    I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.

    Thanks for taking a look.

    Howard
     
    L. Howard, Mar 20, 2014
    #24
  5. L. Howard

    L. Howard Guest

    L. Howard, Mar 20, 2014
    #25
  6. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Thu, 20 Mar 2014 11:00:00 -0700 (PDT) schrieb L. Howard:

    > The sheets of concern at this time are Publish Data, Title Builder and Description Builder.
    > Title code needs to fill column A of Title sheet and column B of Publish.
    > Description code needs to fill column A of Description sheet and column E of publish.
    > Mostly it is doing that but the list seem to be correctly done but not identical.
    > That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.
    > The same for Description.
    > Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.
    > I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.


    at the moment I am a little bit confused.

    Should the list in column A or Title and column B of Publish be the
    same?
    Also for Description and Publish?
    Or should be randomized between running the code?


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 20, 2014
    #26
  7. L. Howard

    L. Howard Guest

    On Thursday, March 20, 2014 11:07:41 AM UTC-7, L. Howard wrote:
    > On Thursday, March 20, 2014 11:00:00 AM UTC-7, L. Howard wrote:
    >



    I believe this is another screw up on my part.

    I have changed:
    Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
    To
    Sheets("Sheet2").Range("B2").Resize(rowsize:=UBound(myArr)) = myArr
    Thinking that was a destination...?

    myArr = Range("A2:A2001")
    Sheets("Sheet2").Range("A2").Resize(rowsize:=UBound(myArr)) = myArr
    Application.ScreenUpdating = True
     
    L. Howard, Mar 20, 2014
    #27
  8. L. Howard

    L. Howard Guest

    On Thursday, March 20, 2014 11:18:42 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Thu, 20 Mar 2014 11:00:00 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > The sheets of concern at this time are Publish Data, Title Builder and Description Builder.

    >
    > > Title code needs to fill column A of Title sheet and column B of Publish.

    >
    > > Description code needs to fill column A of Description sheet and column E of publish.

    >
    > > Mostly it is doing that but the list seem to be correctly done but not identical.

    >
    > > That is: the list on Titles column A and the list on Publish column B are different from one another after each running of the code.

    >
    > > The same for Description.

    >
    > > Also need to be able to run Titles and Description code from Publish sheet. There is a button for each. This seems to somewhat of a problem.

    >
    > > I'll be dealing with the other sheets to do similar work, but hopefully I can use the correctly done code for Titles and Descriptions to guide me.

    >
    >
    >
    > at the moment I am a little bit confused.
    >
    >
    >
    > Should the list in column A or Title and column B of Publish be the
    >
    > same?
    >
    > Also for Description and Publish?
    >
    > Or should be randomized between running the code?
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >


    Each time the code is run for Titles both destinations should be identical lists.

    Titles column A on Titles sheet and column B on Publish sheet.

    Descriptions column A on Descriptions sheet and column E on Publish sheet.

    Howard
     
    L. Howard, Mar 20, 2014
    #28
  9. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Thu, 20 Mar 2014 11:38:22 -0700 (PDT) schrieb L. Howard:

    > Each time the code is run for Titles both destinations should be identical lists.
    >
    > Titles column A on Titles sheet and column B on Publish sheet.
    >
    > Descriptions column A on Descriptions sheet and column E on Publish sheet.


    I hope I understood you correctly.
    Please have a look:
    https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
    for workbook "Title Builder Randomizer rev 2.3.xlsm"


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 20, 2014
    #29
  10. L. Howard

    L. Howard Guest

    On Thursday, March 20, 2014 12:03:50 PM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Thu, 20 Mar 2014 11:38:22 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > Each time the code is run for Titles both destinations should be identical lists.

    >
    > >

    >
    > > Titles column A on Titles sheet and column B on Publish sheet.

    >
    > >

    >
    > > Descriptions column A on Descriptions sheet and column E on Publish sheet.

    >
    >
    >
    > I hope I understood you correctly.
    >
    > Please have a look:
    >
    > https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
    >
    > for workbook "Title Builder Randomizer rev 2.3.xlsm"
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    That is looking pretty good. I still need the Title portion to produce a single phrase instead of six phrases.

    This link demonstrates what the Title output should look like.
    Note column A entries are just one phrase.


    https://www.dropbox.com/s/rftplrkmdwlaeh0/Title One Phrase Example.xlsm

    Howard
     
    L. Howard, Mar 20, 2014
    #30
  11. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Thu, 20 Mar 2014 13:42:20 -0700 (PDT) schrieb L. Howard:

    > That is looking pretty good. I still need the Title portion to produce a single phrase instead of six phrases.
    >
    > This link demonstrates what the Title output should look like.
    > Note column A entries are just one phrase.
    >
    > https://www.dropbox.com/s/rftplrkmdwlaeh0/Title One Phrase Example.xlsm


    I improved the code. Please have another look.


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 20, 2014
    #31
  12. L. Howard

    L. Howard Guest

    On Thursday, March 20, 2014 2:11:01 PM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Thu, 20 Mar 2014 13:42:20 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > That is looking pretty good. I still need the Title portion to produce a single phrase instead of six phrases.

    >
    > >

    >
    > > This link demonstrates what the Title output should look like.

    >
    > > Note column A entries are just one phrase.

    >
    > >

    >
    > > https://www.dropbox.com/s/rftplrkmdwlaeh0/Title One Phrase Example.xlsm

    >
    >
    >
    > I improved the code. Please have another look.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    Yes, that looks great!

    I hope those codes will serve me well in finishing the workbook with the remaining sheets.

    At this point I don't know what all info on the remaining sheet is the stuff to bring to Publish.

    Terrific work in my eyes.

    Thanks much.

    Howard
     
    L. Howard, Mar 20, 2014
    #32
  13. L. Howard

    L. Howard Guest

    Hi Claus,

    Some advice please.

    The "Title Builder" and "Description Builder" worksheets are my focus here.
    I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete.

    12,000 Titles and 2000 Descriptions to column A on their respective sheets.
    The GOOD news is that duplicates are very very rare. Have yet to find one on the Description sheet and Titles I usually get from 0 to 3. These are very acceptable results.

    The BAD news is the length of time to run the codes.

    I have tried Calculation set to manual on both codes and the time is very acceptable, about 1 second.
    The major BAD news with this is that the duplicates occur at a huge and unacceptable level. So I guess there are some necessary calculations not happening and the duplications occur.

    I'm open to suggestions, perhaps do those Titles and Descriptions calculations in a separate workbook/s and transfer the data to the proper columns on the main workbook..??

    This is a Demo main workbook if you need to see anything on it.

    https://www.dropbox.com/s/vz387bfi02dimav/Demo Work Book rev 2.8 tester Drop Box.xlsm

    Thanks.
    Howard
     
    L. Howard, Mar 24, 2014
    #33
  14. L. Howard

    L. Howard Guest

    This should say...

    I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete the transfer to column A.

    Howard
     
    L. Howard, Mar 24, 2014
    #34
  15. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Sun, 23 Mar 2014 19:06:47 -0700 (PDT) schrieb L. Howard:

    > I have found that if I run either Description or Title macros, they take about 190 seconds plus another 90 seconds to complete the transfer to column A.


    if I would know what the workbook should do I could help better.
    Do you need the columns AE:AO?
    I thought in column A should only be 1 sentence?
    Have a look:
    https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
    for workbook "Demo Work Book rev2.9"
    I changed the formulas in AQ and the code for
    "Six_By_Six_Title_Randomizer" and "CopyToA2_2_Titles"

    If you can work with this suggestion you can similiar change the rest of
    the code.


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 24, 2014
    #35
  16. L. Howard

    Claus Busch Guest

    Claus Busch, Mar 24, 2014
    #36
  17. L. Howard

    L. Howard Guest

    I ran the Titles code in your revision and it worked pretty fast, but had about 100 duplicates. I used the "remove duplicates" feature on the Data tab and it found about 100 each time I ran the code and checked.

    So, maybe the revised code with some more code at the end to remove duplicates is the answer. There would only be about 11,900 non duplicated entriesbut that might a workable solution.

    The goal is for Titles to produce 12,000 single phrases in column A, with no duplicates.

    Entries like this are considered a duplicate.

    Greetings to you! Ordering a King Std Top? On Hand 65% savings!
    Greetings to you! Ordering a King Std Top? On Hand 65% savings!

    Entries like these are not duplicates.

    BuenosDias - Fishing for a Contemporary Serta Queen Set? .> $below retail.
    BuenosDias - Pursing a Current Beauty Rest-Bed? $$ under retail.
    Buenos dias! Buying that King Comfort Set? Full Line $$ below retail,

    The goal for Descriptions is to produce 2,000 entries in column A where each entry will be up to 6 individual phrases per entry.

    No duplicates allowed with Descriptions also. And it seems duplicates in the Descriptions are very rare. Probably because of the multiple phrases ineach entry.

    As far as need the columns AE:AO, I cannot say. Speed of the code and avoiding duplicates is paramount concern. If they can be eliminated that is okay.

    I have tested the Titles in a one sheet workbook that does nothing but the 12,000 rows of single phrase entries, and the duplicates range between 0 and 4 on any given run of the code. The well written code spits the 12,000 entries out in about 1 second. That speed and that small amount of duplicates would be acceptable.

    I did notice in the workbook I sent that some of the comments in the code are inaccurate, my bad and I am sorry if that caused confusion. Best to ignore them and the goals for the code is as I have stated above or as close as we can get to them.

    Howard
     
    L. Howard, Mar 24, 2014
    #37
  18. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 24 Mar 2014 06:46:51 -0700 (PDT) schrieb L. Howard:

    > I ran the Titles code in your revision and it worked pretty fast, but had about 100 duplicates. I used the "remove duplicates" feature on the Data tab and it found about 100 each time I ran the code and checked.


    I ran the Titles code also but I get a maximum of 4 duplicates. The
    message of "RemoveDuplicates" is wrong.
    Write in B2 (under "Len")
    =--(Countif($A$2:$A$12001;A2) and copy down to B12001. After code is
    through filter this column for 1.


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 24, 2014
    #38
  19. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 24 Mar 2014 15:57:57 +0100 schrieb Claus Busch:

    > =--(Countif($A$2:$A$12001;A2) and copy down to B12001. After code is
    > through filter this column for 1.


    here is a typo. The formula should be:
    =--(Countif($A$2:$A$12001;A2)>1)


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Prodessional
     
    Claus Busch, Mar 24, 2014
    #39
  20. L. Howard

    L. Howard Guest


    > here is a typo. The formula should be:
    >
    > =--(Countif($A$2:$A$12001;A2)>1)
    >


    I re-downloaded you revised workbook and it works as you say. Using the Remove Duplicates feature agrees with the formula results in the test I did. About six tests.

    And I too never got more than 4 dupes.

    This is acceptable performance as far as I am concerned.

    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.

    If I set the calc to manual, it seems to just repeat the first 20 entries until it reaches 2000 and quite fast. With cacl at automatic the code grinds along for about 4 minutes but I the entries are good, no dupes.

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

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Andy
    Replies:
    1
    Views:
    187
    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:
    101
    GJones
    Nov 19, 2003
  3. mary
    Replies:
    3
    Views:
    137
    Tom Ogilvy
    Jan 19, 2004
  4. jrb

    Deleting blanks and shifting left - cont.

    jrb, Feb 27, 2004, in forum: Excel Programming
    Replies:
    3
    Views:
    209
    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:
    210
    justaguyfromky
    Sep 3, 2006
  6. Ben
    Replies:
    1
    Views:
    195
    Pflugs
    Jul 10, 2007
  7. robs3131

    Issue removing leading and lagging spaces

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

    Blanks and Non blanks

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