Results 1 to 2 of 2

VBS Delimiter overwriting Header Rows (Row1)

Threaded View

  1. #1
    Registered User
    Join Date
    08-21-2005
    MS-Off Ver
    2013
    Posts
    23

    VBS Delimiter overwriting Header Rows (Row1)

    Hello,

    I have column headings in row 1 (Column A-K).
    From Row 2 will have the data.
    If a row has a caret ^, then the macro will transpose.
    Any semicolons, will be delimited.
    When the macro is run, it removes the data in row B1(Line 2). I can have up to 4 semicolons and it will remove the text from Row B1, C1, D1 and E1. I need the macro to leave row 1 alone. Otherwise the macro works. Also, I would like for the macro to place a 1 in all the cells in the Qty (Col F2) IF there is caret.
    The macro, in the below, will place the data in cell but it clears row B1 in this example:
    A2-abc
    B2-456
    A3-BCD
    A4-679

    Example:

    Row 1 Line 1 Line 2 line 3 line 4 line 5 Qty
    Row 2 abc;456^BCD;679 2

    I have tried different things to make the "Line X' to be retained in Row 1 but to no avail and I am a novice. Any assistance would be greatly appreciated!

    Code that is in the macro now is:
    Sub delimit()
    Application.ScreenUpdating = False
    LR = Cells(Rows.Count, 1).End(xlUp).Row
    For r = LR To 2 Step -1
    Set MyCell = Cells(r, 1)
    Arry = Split(MyCell.Value, "^")
    For c = 0 To UBound(Arry)
    If c > 0 Then MyCell.Offset(c, 0).EntireRow.Insert
    MyCell.Offset(c, 0) = Arry(c)
    Next c
    Next r
    
      Columns("A:A").Select
      Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
          TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
          Other:=True, OtherChar _
          :=";", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
            
    End Sub
    Attached Files Attached Files
    Last edited by alansidman; 08-24-2014 at 11:57 AM. Reason: code tags added

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 06-07-2014, 10:07 AM
  2. Need to copy header row in group to all rows below header; stop if blank and repeate
    By jmcaleer10 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2013, 11:09 AM
  3. Copying in sequential rows without overwriting
    By ecarly in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-18-2013, 12:26 PM
  4. Text to Rows Macro using Indentations as Delimiter
    By Meves in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-12-2012, 08:34 AM
  5. Paste formats from row1 to all rows with data
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-03-2008, 05:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1