Find, Cut and Paste (Excel 2003 SP2)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote