Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a macro to delete multiple rows? | Excel Discussion (Misc queries) | |||
Create a macro to delete rows if value is less than a specified nu | Excel Worksheet Functions | |||
create macro - delete all rows with zero in column L | Excel Programming | |||
create macro - if cell is 0 delete row | Excel Worksheet Functions | |||
have Macro automatically delete and create sheets | Excel Programming |