Issue with blanks and spaces

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

  1. L. Howard

    L. Howard Guest

    I am running code that produces a huge list (short phrases) in column A2 and down.

    In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells.

    However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces.

    I am fairly certain manipulating the data in column A is the better way to cope with the "blank looking" cells that have the spaces.

    Where I am stumped is the cells with the short phrases have spaces that are needed. So a blanket remove spaces from column A would not work.

    I cannot figure how to distinguish the good spaces from the bad spaces then remove the cells with the bad spaces.

    The end result being a list of the phrases in A2 and down with no blanks.

    I looked for examples of filter code and for If IsText but did not come up with anything.

    Also, I cannot be sure it will always be 5 spaces each time.

    Thanks.
    Howard
     
    L. Howard, Mar 16, 2014
    #1
    1. Advertisements

  2. L. Howard

    Auric__ Guest

    L. Howard wrote:

    > I am running code that produces a huge list (short phrases) in column A2
    > and down.
    >
    > In the normal operation of the code, there are times when it produces
    > the phrases as wanted and other times due to the data the code is
    > working on, it produces the phrases and apparently blank cells.
    >
    > However, the cells actually have 5 spaces in them. When I look the
    > sheet over it is evident why there are 5 spaces. To fix the problem by
    > altering the main code would for sure require a massive rewrite, if I
    > could even explain what is causing the spaces.
    >
    > I am fairly certain manipulating the data in column A is the better way
    > to cope with the "blank looking" cells that have the spaces.
    >
    > Where I am stumped is the cells with the short phrases have spaces that
    > are needed. So a blanket remove spaces from column A would not work.
    >
    > I cannot figure how to distinguish the good spaces from the bad spaces
    > then remove the cells with the bad spaces.
    >
    > The end result being a list of the phrases in A2 and down with no
    > blanks.
    >
    > I looked for examples of filter code and for If IsText but did not come
    > up with anything.
    >
    > Also, I cannot be sure it will always be 5 spaces each time.


    At the spot where you put the text ino the cell, try something like this:

    If Len(Trim$(random_phrase)) > 0 Then ActiveCell.Value = random_phrase

    --
    Thanks for the feedback, dream-killer.
     
    Auric__, Mar 17, 2014
    #2
    1. Advertisements

  3. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Sun, 16 Mar 2014 16:06:20 -0700 (PDT) schrieb L. Howard:

    > I am running code that produces a huge list (short phrases) in column A2 and down.
    >
    > In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells.
    >
    > However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces.


    can we see your code or your workbook?


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Mar 17, 2014
    #3
  4. L. Howard

    L. Howard Guest

    On Sunday, March 16, 2014 11:26:51 PM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Sun, 16 Mar 2014 16:06:20 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > I am running code that produces a huge list (short phrases) in column A2 and down.

    >
    > >

    >
    > > In the normal operation of the code, there are times when it produces the phrases as wanted and other times due to the data the code is working on, it produces the phrases and apparently blank cells.

    >
    > >

    >
    > > However, the cells actually have 5 spaces in them. When I look the sheet over it is evident why there are 5 spaces. To fix the problem by altering the main code would for sure require a massive rewrite, if I could even explain what is causing the spaces.

    >
    >
    >
    > can we see your code or your workbook?
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --
    >
    > Win XP PRof SP2 / Vista Ultimate SP2
    >
    > Office 2003 SP2 /2007 Ultimate SP2


    >
    > can we see your code or your workbook?
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    Thanks Auric, I tried to incorporate your suggestion but couldn't make it work.
    Changed the random_phrase to Tlt in the Sub Titles_Col_A and to (b) in the other code Claus wrote but no go.

    Not even sure I had it in the correct spot.

    Claus, you may recognize the major code here. If you look on the sheet at cell D46 you will see a purple section that is blank. This is what causes the 6 spaces in the column A output. The blank section (maybe two sections) would be normal use.

    I tried putting a "false" character in each purple cell, * for instance, and then tried a 'find and clear contents' to try to get it to a true blank but just isn't my day I guess.

    Thanks for taking a look.


    https://www.dropbox.com/s/z7zqw8odblsmhdr/Title Builder Randomizer rev 1.0 Drop Box.xlsm

    Howard

    I often get an error message about trouble posting and to try again later. Wonder if it is something on my end or what?? Has a Reload option that wipes everything out and I have to start over again from scratch.
     
    L. Howard, Mar 17, 2014
    #4
  5. L. Howard

    L. Howard Guest

    Woops, I left this line in the code, which runs amok, delete it and click the Titles button to produce the list in column A

    If Len(Trim$(Tlt)) > 0 Then ActiveCell.Value = Tlt



    For Each Tlt In aeRng
    Range("A" & n).Resize(6).Value = .Transpose(.Index(Tlt.Resize(, 11), 0, Array(1, 3, 5, 7, 9, 11)))
    If Len(Trim$(Tlt)) > 0 Then ActiveCell.Value = Tlt
    n = n + 6
    Next Tlt


    Howard
     
    L. Howard, Mar 17, 2014
    #5
  6. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 17 Mar 2014 00:08:00 -0700 (PDT) schrieb L. Howard:

    > If Len(Trim$(Tlt)) > 0 Then ActiveCell.Value = Tlt


    why has range D46:N65 no data?
    Or how should it be filled?


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Mar 17, 2014
    #6
  7. L. Howard

    L. Howard Guest

    On Monday, March 17, 2014 2:14:59 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Mon, 17 Mar 2014 00:08:00 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > If Len(Trim$(Tlt)) > 0 Then ActiveCell.Value = Tlt

    >
    >
    >
    > why has range D46:N65 no data?
    >
    > Or how should it be filled?
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >

    On Monday, March 17, 2014 2:14:59 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Mon, 17 Mar 2014 00:08:00 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > If Len(Trim$(Tlt)) > 0 Then ActiveCell.Value = Tlt

    >
    >
    >
    > why has range D46:N65 no data?
    >
    > Or how should it be filled?


    Hi Claus,

    Range D46:N65 is blank as a matter of normal use. It is filled manually by placing certain words in each column D, F, H, J, L, M. Then a single word from each column produces a short phrase.

    I don't know exactly why it is blank, except the user may only want 5 individual short phrases in each result returned to column A. May also only want 4 short phrases in the column A return, so 2 of those colored ranges could be blank.

    You most likely picked up that each of those section (above and below the D46:N65 blank range) provide the words for a randomly selected phrase. So if all sections are filled with words then there would be 6 short phrases in each cell in column A.

    That is why I thought the solution should be manipulating the data in column A rather rewrite all the main code. The code works very well as written.

    Howard
     
    L. Howard, Mar 17, 2014
    #7
  8. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 17 Mar 2014 05:19:17 -0700 (PDT) schrieb L. Howard:

    > Range D46:N65 is blank as a matter of normal use. It is filled manually by placing certain words in each column D, F, H, J, L, M. Then a single word from each column produces a short phrase.


    then fill range AQ only if there are values in range D:N.
    Instead of formula use following macro:

    Sub FillAQ()
    Dim rngC As Range
    Dim i As Long
    Dim myStr As String

    For Each rngC In Range("AQ2:AQ131")
    myStr = ""
    For i = 19 To 29 Step 2
    If Len(Cells(rngC.Row, i)) > 0 Then
    myStr = myStr & Cells(rngC.Row, i) & " "
    End If
    Next
    myStr = RTrim(myStr)
    rngC = myStr
    Next
    End Sub

    And change the Randomize macro to:

    Sub Six_By_Six_Title_Randomizer()
    '/ by Claus
    Dim a(19) As Variant, b, c, d, e, f
    Dim Small As Integer, Big As Integer
    Dim i As Long, j As Long, n As Long, k As Long
    Dim conT As Long
    Dim iI As Long
    Dim arrOut As Variant
    Dim myCol As Long

    Application.ScreenUpdating = False

    [AE2:AO2010,A2:A12100].ClearContents

    Small = 1
    For conT = 1 To 100
    For n = 2 To 112 Step 22
    For k = 3 To 13 Step 2

    Big = Small + 19
    j = 0
    For i = Small To Big
    a(j) = i
    j = j + 1
    Next
    b = a: Randomize
    d = UBound(b)
    For c = 0 To d
    e = Int(d * Rnd) + 1
    f = b(c): b(c) = b(e): b(e) = f
    Next

    Cells(n, k).Resize(rowsize:=20) = WorksheetFunction.Transpose(b)

    Small = Small + 20

    Next 'k

    Next 'n

    FillAQ

    myCol = 31
    For iI = 2 To 112 Step 22
    arrOut = Range("AQ" & iI).Resize(rowsize:=20)
    Cells(Rows.Count, myCol).End(xlUp).Offset(1, 0) _
    .Resize(rowsize:=20) = arrOut
    myCol = myCol + 2
    Next

    Small = 1
    Next 'conT

    Application.ScreenUpdating = True

    End Sub


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Mar 17, 2014
    #8
  9. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 17 Mar 2014 13:44:55 +0100 schrieb Claus Busch:

    >> Range D46:N65 is blank as a matter of normal use. It is filled manually by placing certain words in each column D, F, H, J, L, M. Then a single word from each column produces a short phrase.


    have a look:
    https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
    for workbook "Title Builder Randomizer rev 2.0.xlsm"


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Mar 17, 2014
    #9
  10. L. Howard

    L. Howard Guest


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


    Wonderful!!!

    I will be adamant to the user who has made this work so well.

    I should have known that it is best fix the problem at the source instead of patching up poor results.

    I'll study the old code and new to see if I can gather some insight on how you did that. Most of it remains over my head, but I pick up a little bit from time to time.

    Thanks Claus.

    Regards,
    Howard
     
    L. Howard, Mar 17, 2014
    #10
  11. L. Howard

    L. Howard Guest


    >
    > this code only reads cells with values into the array instead of 6
    >
    > items.
    >
    > And so the range is resized with Ubound(arrOut) +1 you get no empty
    >
    > cells.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    So I'm thinking that is done here where the space " " is replaced with nothing "" and if still greater than 0 it means there is text so it reads it into the array.

    If Len(Replace(Tlt.Offset(0, i), " ", "")) > 0 Then
    ReDim Preserve arrOut(j)
    arrOut(j) = Tlt.Offset(0, i)
    j = j + 1
    End If
     
    L. Howard, Mar 17, 2014
    #11
  12. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Mon, 17 Mar 2014 15:20:17 -0700 (PDT) schrieb L. Howard:

    > So I'm thinking that is done here where the space " " is replaced with nothing "" and if still greater than 0 it means there is text so it reads it into the array.
    >
    > If Len(Replace(Tlt.Offset(0, i), " ", "")) > 0 Then
    > ReDim Preserve arrOut(j)
    > arrOut(j) = Tlt.Offset(0, i)
    > j = j + 1
    > End If


    yes, that is the only change I made


    Regards
    Claus B.
    --
    Win XP PRof SP2 / Vista Ultimate SP2
    Office 2003 SP2 /2007 Ultimate SP2
     
    Claus Busch, Mar 17, 2014
    #12
  13. L. Howard

    L. Howard Guest

    I need another nudge.
    Want to transfer data workbook Y.
    A list of target worksheets in book Y is in column AU of the source workbook/worksheet.
    The target column for each target sheet is next to it in column AV.

    The code errors out on this line where I have put "c" and c in place of "what goes here??"

    Set wksTarget = wkbTarget.Sheets("What goes here??")
    Set wksTarget = wkbTarget.Sheets("c")
    Set wksTarget = wkbTarget.Sheets(c)

    None work.

    The Msgboxes both return a correct sheet name and a column number.
    Which is Allee & 1 as they are the first entries of the list.

    Thanks,
    Howard


    Sub Transfer_Titles()
    Dim myRng As Range
    Dim rngC As Range
    Dim i As Long
    Dim myArr() As Variant

    Dim wksSource As Worksheet, wksTarget As Worksheet
    Dim wkbSource As Workbook, wkbTarget As Workbook
    Dim rngSource As Range, rngTarget As Range

    Dim c As Range
    Dim trgWs As Range
    Dim trgCol As Long

    Set myRng = Range("A2:A12100")

    For Each rngC In myRng
    ReDim Preserve myArr(myRng.Cells.Count - 1)
    myArr(i) = rngC
    i = i + 1
    Next

    '/ List of target sheet names in column AU2:AU21
    '/ Destination column for each target sheet is in AV next to sheet name

    Set wkbSource = Workbooks("Title Builder Randomizer rev 2.0 xfer titles.xlsm")
    Set wkbTarget = Workbooks("Y.xlsm")

    For Each c In Range("AU2:AU21")
    MsgBox c
    trgCol = c.Offset(0, 1)
    MsgBox trgCol

    Set wksTarget = wkbTarget.Sheets("What goes here??")

    With wksSource
    wksTarget.Cells(2, trgCol).Resize(rowsize:=myRng.Cells.Count) _
    = WorksheetFunction.Transpose(myArr)
    End With
    Next 'c
    End Sub
     
    L. Howard, Mar 18, 2014
    #13
  14. L. Howard

    L. Howard Guest

    Also, I added this in case the workbook Y was not open.

    If Not IsFileOpen("C:\Users\Howard Kittle\Documents\Y.xlsm") Then
    Workbooks.Open ("C:\Users\Howard Kittle\Documents\Y.xlsm")
    End If

    It does indeed open the workbook if not open and the code runs on down until it errors out as I posted above.

    However, the Msgboxes both return blank until the code is run again, then correct data is indicated. (of course it still errors out on that same line)

    I don't understand why it prevents the variables from returning in the msgboxes on the opening run.

    Howard
     
    L. Howard, Mar 18, 2014
    #14
  15. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Tue, 18 Mar 2014 14:41:45 -0700 (PDT) schrieb L. Howard:

    > I need another nudge.
    > Want to transfer data workbook Y.
    > A list of target worksheets in book Y is in column AU of the source workbook/worksheet.
    > The target column for each target sheet is next to it in column AV.


    Then try:

    Sub Transfer_Titles()

    Dim Dest As Range
    Dim i As Long
    Dim myArr() As Variant
    Dim arrDest As Variant
    Dim LRow As Long
    Dim wkbSource As Workbook, wkbTarget As Workbook

    LRow = Cells(Rows.Count, 1).End(xlUp).Row

    myArr = Range("A2:A" & LRow)

    '/ List of target sheet names in column AU2:AU21
    '/ Destination column for each target sheet is in AV next to sheet name

    Set wkbSource = ThisWorkbook
    Set wkbTarget = Workbooks("Y.xlsm")

    arrDest = Range("AU2:AV21")
    For i = LBound(arrDest) To UBound(arrDest)
    Set Dest = wkbTarget.Sheets(arrDest(i, 1)).Cells(2, arrDest(i, 2))
    Dest.Resize(rowsize:=UBound(myArr)) = myArr
    Dest.EntireColumn.AutoFit
    Next 'i
    End Sub


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

    Claus Busch Guest

    Hi Howard,

    Am Wed, 19 Mar 2014 09:11:24 +0100 schrieb Claus Busch:

    Enter a code line with

    With ThisWorkbook.Sheets("Title Builder")

    in case the sheet "Title Builder" is not the active sheet
    Then don't forget the dots in front of the ranges

    > LRow = Cells(Rows.Count, 1).End(xlUp).Row


    if all sections in all colors are filled you have more than 12100 rows,
    otherwise you can have less.


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

    L. Howard Guest


    > > The target column for each target sheet is next to it in column AV.

    >
    >
    >
    > Then try:
    >
    >
    >
    > Sub Transfer_Titles()
    >
    >
    >
    > Dim Dest As Range
    >
    > Dim i As Long
    >
    > Dim myArr() As Variant
    >
    > Dim arrDest As Variant
    >
    > Dim LRow As Long
    >
    > Dim wkbSource As Workbook, wkbTarget As Workbook
    >
    >
    >
    > LRow = Cells(Rows.Count, 1).End(xlUp).Row
    >
    >
    >
    > myArr = Range("A2:A" & LRow)
    >
    >
    >
    > '/ List of target sheet names in column AU2:AU21
    >
    > '/ Destination column for each target sheet is in AV next to sheet name
    >
    >
    >
    > Set wkbSource = ThisWorkbook
    >
    > Set wkbTarget = Workbooks("Y.xlsm")
    >
    >
    >
    > arrDest = Range("AU2:AV21")
    >
    > For i = LBound(arrDest) To UBound(arrDest)
    >
    > Set Dest = wkbTarget.Sheets(arrDest(i, 1)).Cells(2, arrDest(i, 2))
    >
    > Dest.Resize(rowsize:=UBound(myArr)) = myArr
    >
    > Dest.EntireColumn.AutoFit
    >
    > Next 'i
    >
    > End Sub
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >



    Oh yes! That works very quick. Nice. About 0.623 seconds to transfer 10,000 rows to twenty sheets.

    I wrote a couple of lines to recalc the data between each sheet transfer so that each sheet got a unique set of data and the time was 36 seconds. That seems quite reasonable to me given it is the recalc that takes the time, not the transfer.

    Thanks Claus. The array speed is always impressive.

    Howard
     
    L. Howard, Mar 19, 2014
    #17
  18. L. Howard

    L. Howard Guest

    On Wednesday, March 19, 2014 1:22:46 AM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Wed, 19 Mar 2014 09:11:24 +0100 schrieb Claus Busch:
    >
    >
    >
    > Enter a code line with
    >
    >
    >
    > With ThisWorkbook.Sheets("Title Builder")
    >
    >
    >
    > in case the sheet "Title Builder" is not the active sheet
    >
    > Then don't forget the dots in front of the ranges
    >
    >
    >
    > > LRow = Cells(Rows.Count, 1).End(xlUp).Row

    >
    >
    >
    > if all sections in all colors are filled you have more than 12100 rows,
    >
    > otherwise you can have less.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --
    >
    > Vista Ultimate SP2 / Windows7 SP1
    >
    > Office 2007 Ultimate SP3 / 2010 Prodessional



    Hi Claus,

    On a different sheet in the same project, I am trying to output column AQ to both column A and sheet 2 column B of the same workbook. This sheet is very almost identical to the Titles sheet except here it is returning up to six short phrases to the taget cells.

    This is one of many attempts to get it to write to sheet2.
    Is this a case like you describe above.

    Writes to sheet 1 just fine, the active sheet.

    Sub A2_Down_Copy()
    Dim lRowCount
    lRowCount = Cells(Rows.Count, "AE").End(xlUp).Row

    With Sheets("Sheet1").Range("A2").Resize(lRowCount)
    .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)": .Value = .Value
    End With

    With Sheets("Sheet2").Range("B2").Resize(lRowCount)
    .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)": .Value = .Value
    End With

    End Sub

    Thanks.
    Howard
     
    L. Howard, Mar 19, 2014
    #18
  19. L. Howard

    Claus Busch Guest

    Hi Howard,

    Am Wed, 19 Mar 2014 15:52:12 -0700 (PDT) schrieb L. Howard:

    > This is one of many attempts to get it to write to sheet2.
    > Is this a case like you describe above.


    I would do it with the formula only once.
    Try:

    Sub A2_Down_Copy()
    Dim lRowCount
    Dim myArr As Variant

    With Sheets("Sheet1")
    lRowCount = .Cells(Rows.Count, "AE").End(xlUp).Row
    With .Range("A2").Resize(lRowCount)
    .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)"
    .Value = .Value
    myArr = Range("A2:A" & lRowCount)
    End With
    End With

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

    End Sub


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

    L. Howard Guest

    On Wednesday, March 19, 2014 11:37:20 PM UTC-7, Claus Busch wrote:
    > Hi Howard,
    >
    >
    >
    > Am Wed, 19 Mar 2014 15:52:12 -0700 (PDT) schrieb L. Howard:
    >
    >
    >
    > > This is one of many attempts to get it to write to sheet2.

    >
    > > Is this a case like you describe above.

    >
    >
    >
    > I would do it with the formula only once.
    >
    > Try:
    >
    >
    >
    > Sub A2_Down_Copy()
    >
    > Dim lRowCount
    >
    > Dim myArr As Variant
    >
    >
    >
    > With Sheets("Sheet1")
    >
    > lRowCount = .Cells(Rows.Count, "AE").End(xlUp).Row
    >
    > With .Range("A2").Resize(lRowCount)
    >
    > .Formula = "=CONCATENATE(AE2&AG2&AI2&AK2&AM2&AO2)"
    >
    > .Value = .Value
    >
    > myArr = Range("A2:A" & lRowCount)
    >
    > End With
    >
    > End With
    >
    >
    >
    > Sheets("Sheet2").Range("B2").Resize(lRowCount) = myArr
    >
    >
    >
    > End Sub
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --


    Thanks Claus. That works well for me. Sheet 1 copy is perfect.

    The Sheet 2 copy was producing a ghost #N/A in row 2002. I did this and it went away.

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

    Row 2 and 2001 on both sheets are identical each time I test.

    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.

    But like your code, always top notch.

    Thanks again.

    Howard
     
    L. Howard, Mar 20, 2014
    #20
    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:
    157
    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:
    77
    GJones
    Nov 19, 2003
  3. mary
    Replies:
    3
    Views:
    110
    Tom Ogilvy
    Jan 19, 2004
  4. jrb

    Deleting blanks and shifting left - cont.

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

    Issue removing leading and lagging spaces

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

    Blanks and Non blanks

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