Macro to manage data from import

[tonyjb123]

This is what I am trying to do.
Pulling data from an import Range A1 to L4800. using excel 2003

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

• If Column A has the string “0001:
  • value of the contents of the corresponding row in Column A to be copied to column S same row
  • value of the contents of the corresponding row in Column B to be copied to column T same row
  • value of the contents of the corresponding row in Column E to be copied to column W same row

<o></o>

• If Column B has the string “Account Total”
  • value of the contents of the corresponding row in Column E,G,H,I to be copied to column W,X,Y,Z repsectively same row.
• If Column A does not have the string “0001 and If Column B does not have the string “Account Total, place 0 in column U
• Then I want to run this macro
  • Sub Delete_Zero_Rows()
  • Dim LastRow As Long, r As Long
  • LastRow = Cells(Rows.Count, "U").End(xlUp).Row
  • For r = LastRow To 1 Step –1
  • If Cells(r, "U") = 0 Then
  • Rows(r).EntireRow.Delete
  • End If
  • Next r
  • End Sub

 

[Cindy Ellis]

Hello, and welcome to Mr. Excel!
If I follow your question correctly, this can be done with simple formulas in appropriate cells, copied down (drag the lower right hand corner of the cell, or just double-click the lower right-hand corner if the cells are adjacent to a continuous column).
I'm going to assume the first data row is row 2, to allow for headings. Adjust the row number in the following formulas appropriate to your needs.

In cell S2:
=if(A2="0001", A2,"")
In cell T2:
=if(A2="0001", B2,"")
In cell W2:
=if(or(A2="0001", B2="Account Total"), E2,"")
In cell X2:
=if(B2="Account Total", G2,"")
In cell Y2:
=if(B2="Account Total", H2,"")
In cell Z2:
=if(B2="Account Total", I2,"")
In cell U2:
=if(AND(A2<>"0001", B2<>"Account Total"), 0,"")

Given that the next step is to delete the rows with 0 in column U, I would use autofilter instead of stepping backwards through the rows. Something like the following (test in a copy of your workbook, since data is permanently deleted):

    lastrow = ActiveSheet.Cells(Rows.Count, 21).End(xlUp).Row
    ActiveSheet.Range("$U$1:$U$" & lastrow).AutoFilter Field:=1, Criteria1:="0"
    Rows("2:" & lastrow).Delete Shift:=xlUp
    ActiveSheet.Range("$U$1:$U$" & lastrow).AutoFilter
    Range("U2").Select

Hope that helps,
Cindy

 

[tonyjb123]

Hi Cindy I use something similar, but I am looking for a macro to automate the process. =IF(ISNUMBER(SEARCH("total",$B2)),F2,"" and so for each cell that has the data I need.
The sheet has from 6000 to 8000 rows. I would really like to automate the process. Copying and pasting takes a lot of time I just dont have. If I had a macro doing this I could be more efficent . Unfortunatly I am stuck with this process until the new year.

Thanks Tony

 

[tonyjb123]

Thanks for the welcome.
One more question will the autofilter function Cut and paste data to an ajoining row based on a filter? The filter would be if column C has string "AP" and Row F does not have "405" "303" or "101" , cut value in row E and paste in row D. I have never considered using auto filter to the level you are using it.