Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find, Cut and Paste (Excel 2003 SP2)

    Hi all,

    I have recorded a macro to import a txt file but the macro recorder is unable to record the find function. Is there a way to record the find function?
    What I want to accomplish is to find the characters " UT* ". it range from " UTA " to " UTZ ", cut this and paste it to the next cell on the row, and have it fill down

    Secondly, I want to delete those rows whose ID are not " UT* " as well as those rows whixh are the text report heading ( highlighted in the attached )
    I have attached a sample copy and have highlighted the cell showing the movement upon finding the cell with the desired characters .

    Appreciate your assistance in this.

    Thanks in advance.

    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find, Cut and Paste (Excel 2003 SP2)

    Why are rows 248-251 filled in and , but not 179-180?

    Which rows should be deleted? It sounds like all rows but the ones just filled in is this correct. I don't see how the segment highlighted in green would stay if only rows with UT* in them are kept. How are these rows different from all the other rows to delete? What is different about this "page 22 header" from all the other page headers which you did not color? What defines the "header row"?

    Why don't you attach a workbook with the starting file and what you want at the end and any intermediates you may need to help explain how to get from start to end

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find, Cut and Paste (Excel 2003 SP2)

    Hi Steve,

    Thanks for looking into this.

    First of all, rows 179-180 should also be fill in, I have missed that out, my apology.
    Yes, all rows including the segment highlighted in green should be delete except the ones which were filled in. I've highligted the green segment
    because the data after the segment is actually continue from the data before the green segment.
    There is no different from " page 22 header ' from all the other page header. I need to keep only one header.

    I have attached a sample copy, sheet 1 show after importing the text file and sheet 2 show the desired result that I am looking for.
    The macro should find the ID of " UT* ", ie " UTA " to " UTZ ", cut and paste to next cell upon finding the IDs, and fill down to the rows under the same ID header.
    Is it possible just to copy those rows with the ID of " UT* " to sheet 2 instead of deleting all the rows that don't fall under these IDs. This would be a better option
    for me to fall back and check the original dataset in case users have tampered with the dataset.

    Hope this is clear and let me know if you requires further info or clarification.

    Thanks in advance for your help.

    cheers, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find, Cut and Paste (Excel 2003 SP2)

    Just to clarify:
    So you don't want to delete 2 rows from the "page header" but keep them (this was not in your orginal request)

    Also why is original row Sheet!178 not deleted (it is Sheet2!18)? It does not have a UT* in col G?

    Steve

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find, Cut and Paste (Excel 2003 SP2)

    Presuming thea Sheet!178 was an error on should have been deleted, this code seems to work:

    Steve
    Option Explicit
    Sub MacroForTesta()
    Dim wks As Worksheet
    Dim rng As Range
    Dim lRow As Long

    ActiveSheet.Copy Before:=Sheets(1)
    Set wks = ActiveSheet
    With wks
    lRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
    .Rows("1:7").EntireRow.Delete
    .Rows("2").EntireRow.Insert
    .Range("A1").EntireColumn.Insert

    .Range(.Range("A3"), .Cells(lRow, 1)).Formula = _
    "=IF(ISNUMBER(SEARCH(""PAGE"",RC[8])),1,IF(AND(R[-1]C>0,R[-1]C<10),1+R[-1]C,0))"

    .Columns(1).AutoFilter Field:=1, Criteria1:="<>0"
    .Rows("3:" & lRow).EntireRow.Delete
    .Columns(1).EntireColumn.Delete


    .Columns("G").EntireColumn.Insert
    .Range("G3") = .Range("H3")
    .Range("G4:G" & lRow).FormulaR1C1 = _
    "=IF(RC[-1]="""",""del"",IF(ISNUMBER(SEARCH(""UT*"",RC[-1])),RC[-1],IF(ISNUMBER(SEARCH(""UT*"",R[-1]C)),R[-1]C,""del"")))"
    .Columns("H").EntireColumn.Delete
    .Columns("G").AutoFilter Field:=1, Criteria1:="del"
    .Rows("4:" & lRow).EntireRow.Delete
    .Columns("G").AutoFilter
    .Rows(2).EntireRow.Delete
    End With
    Set wks = Nothing
    Set rng = Nothing
    End Sub

Posting Permissions

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