create a macro that looks for data and delete row

[Guest]

how can i create a macro that look for cells containing "Balance:" in column
N, select cell, delete the row, continue doing the same procedure until there
are no more cells containing "Balance:" Thanks.

 

[PCLIVE]

You can start with something like this and then sort your data.

For Each cell In Range("N1:N" & Range("N65536").End(xlUp).Row)
If cell.Value = "Balance" _
Then
cell.EntireRow.ClearContents
Else:
End If
Next cell

Regards,
Paul

 

[Zone]

Paste this sub into a standard module. It does not select anything,
because there is no need to. James

Sub DelRows()
Dim j As Long
For j = Cells(65536, "n").End(xlUp).Row To 1 Step -1
If Cells(j, "n") = "Balance:" Then Rows(j).EntireRow.Delete
Next j
End Sub

 

[Guest]

can u help me on this:
let's say i have rows like these:
B C D E
3020 0 28TH 28TH ST

3020 0 ACME ACME AWNING

The macro should find 3020 in column B, take cells in colums B C D E in that
row and bring them one
row down, continue the same procedure over and over

thanks

 

[Zone]

We're not really supposed to handle more than one issue per posting. I
suggest that you post this question separately. Also, answer these
questions when you repost: 1. It appears there is an empty row below
each row, so it will not be necessary to insert a new row. Is that
right? 2. Do you want the contents of C,D, and E moved below, or
copied below? 3. Do you need the formatting copied as well, or have
you already formatted the entire columns, C,D, and E? James

 

[Guest]

1. it is not necessary to insert a row
2. contents move below
3. need formatting copied as well
thanks so much for your help

 

[Zone]

Give this a try. I'm presuming you're working on the active worksheet.
Copy and paste this code into a standard module. James

Sub CopyBelow()
Dim k As Long, myrg As Range
For k = Cells(65536, "b").End(xlUp).Row To 2 Step -1
If Cells(k - 1, "b") = 3020 Then
Set myrg = Range("c" & CStr(k - 1) & ":e" & CStr(k - 1))
myrg.Cut Destination:=Cells(k, "c")
End If
Next k
End Sub

 

[Zone]

I misread your post. I thought you only wanted to copy the cells in
columns c,d, and e.
Corrected code below. James

Sub CopyBelow()
Dim k As Long, myrg As Range
For k = Cells(65536, "b").End(xlUp).Row To 2 Step -1
If Cells(k - 1, "b") = 3020 Then
Set myrg = Range("b" & CStr(k - 1) & ":e" & CStr(k - 1))
myrg.Cut Destination:=Cells(k, "b")
End If
Next k
End Sub

 

[Guest]

thanks a lot, you are the best!

Give this a try. I'm presuming you're working on the active worksheet.
Copy and paste this code into a standard module. James

Sub CopyBelow()
Dim k As Long, myrg As Range
For k = Cells(65536, "b").End(xlUp).Row To 2 Step -1
If Cells(k - 1, "b") = 3020 Then
Set myrg = Range("c" & CStr(k - 1) & ":e" & CStr(k - 1))
myrg.Cut Destination:=Cells(k, "c")
End If
Next k
End Sub

 

[James]

Paul,

Is it possible for you to tell me if rather than deleting the entire row, to
delete the values in columns?

i.e. I want to delete the values in a row but maintain a formula that is in
Column A. Delete values in columns B through to ...)

Thanks

James

 

[Don Guillett]

For Each c In Range("N1:N" & cells(rows.count).End(xlUp).Row)
'below is ONE line
If c.Value = "Balance" Then
range(cells(c.row,"b"),cells(c.row,"z")).ClearContents
Next c

 

[Don Guillett]

One line or as shown here add
end if
before next c