macro for deleting specific columns & rows. Help. Please. No Clue.

[Andrew C]

I'll try to be clear, was thown into this task and need some help. I have
tried looking around for help but never saw a straight solution.

I get a daily .csv file that I need to save after I manually delete certain
info. The file has columns A - AA (Always), but can vary on rows. Column A
has multiple text entries, the only one I need is titled Parent. After that
they have a numeric value in column V, I must delete all rows with the value
of 0 (the actual # zero, there are numbers & zeros in this column, need to
get rid of rows with 0). Then the only columns I NEED are
A,B,D,K,L,N,O,P,Q,R,S,V,W (C,E,F,G,H,I,J,M,T,U,X,Y,Z,AA need to be deleted)

I hop e this is clear, and I really hope that there is a easy solution to
some sort of macro I can run to automate this process on a daily basis to
make my life a little easier.

Thanks,
Drew

 

[Mike H]

Andrew,

Right click your sheet tab, view code and paste this in and run it.

Sub delete_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PARENT" And c.Offset(, 21) <> "" _
And c.Offset(, 21) = 0 Then

If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If

Range("C:C,E:J,M:M,T:U,X:AA").Delete
End Sub

Mike

 

[Eduardo]

Hi Andrew,
Take a look at Ron web

http://www.rondebruin.nl/delete.htm

 

[Andrew C]

Mike-
Thanks for the quick response, your input is a huge help. Soooooo
close, but one problem. The 0 value rows were deleted, the correct columns
were deleted, but the rows in column A were not properly "filtered". I JUST
need the rows with the word "Parent" in column A. After running your code, it
looks like it did not delete the rows with values other than "Parent".

Again, thanks for the help. Hopefully you can respond back with a fix.

Drew

 

[Mike H]

Andrew,

Try this.

Sub delete_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PARENT" And c.Offset(, 21) <> "" _
And c.Offset(, 21) = 0 Or UCase(c.Value) <> "PARENT" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.delete
End If

Range("C:C,E:J,M:M,T:U,X:AA").Delete
End Sub

Mike

 

[Andrew C]

Perfect! Thanks so much.

Drew

Andrew,

Try this.

Sub delete_Me()
Dim copyrange As Range, lastrow As Long
lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "PARENT" And c.Offset(, 21) <> "" _
And c.Offset(, 21) = 0 Or UCase(c.Value) <> "PARENT" Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.delete
End If

Range("C:C,E:J,M:M,T:U,X:AA").Delete
End Sub

Mike