create a macro that looks for data and delete row
 

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.

create a macro that looks for data and delete row
 

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

"andresg1975" wrote in message
...
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.

create a macro that looks for data and delete row
 

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

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" wrote:

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

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" wrote:

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

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

Zone wrote:
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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

thanks a lot, you are the best!

"Zone" wrote:

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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" wrote:

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

andresg1975 wrote:
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.

create a macro that looks for data and delete row
 

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
--
J


"PCLIVE" wrote:

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

"andresg1975" wrote in message
...
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.

create a macro that looks for data and delete row
 

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")).ClearCont ents
Next c


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
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
--
J


"PCLIVE" wrote:

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

"andresg1975" wrote in message
...
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.

create a macro that looks for data and delete row
 

One line or as shown here add
end if
before next c
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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")).ClearCont ents
Next c


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
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
--
J


"PCLIVE" wrote:

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

"andresg1975" wrote in message
...
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.