| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to Delete Part of Row with Data
Hi,
I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
Hi..
If I understand you correctly Current period is entered in column H and the data in Column J to M. then the following should work, but can be easily amended. Sub currentperiod() Dim count As Integer count = 2 ' Set this to the row number you want to start from Do Until Cells(count, 8) = "" ' 8 indicates the column number If Cells(count, 8) = "Current Period" Then Range(Cells(count, 10), Cells(count, 14)).Select Selection.ClearContents Range(Cells(count + 1, 10), Cells(count + 1, 14)).Select Selection.Copy Cells(count, 10).Select ActiveSheet.Paste Range(Cells(count + 1, 10), Cells(count + 1, 14)).EntireRow.Delete count = count + 1 Else count = count + 1 End If Loop End Sub Regards Mick "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
Hi ,
The code seems to make sense but it doesn't delete the text "Current Period" in Col H and nor does it copy from the line below. The number 1 to 4 in my question are data items. Thanks, Manir "Symbiosis" wrote: Hi.. If I understand you correctly Current period is entered in column H and the data in Column J to M. then the following should work, but can be easily amended. Sub currentperiod() Dim count As Integer count = 2 ' Set this to the row number you want to start from Do Until Cells(count, 8) = "" ' 8 indicates the column number If Cells(count, 8) = "Current Period" Then Range(Cells(count, 10), Cells(count, 14)).Select Selection.ClearContents Range(Cells(count + 1, 10), Cells(count + 1, 14)).Select Selection.Copy Cells(count, 10).Select ActiveSheet.Paste Range(Cells(count + 1, 10), Cells(count + 1, 14)).EntireRow.Delete count = count + 1 Else count = count + 1 End If Loop End Sub Regards Mick "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
Hi,
Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
I'm getting old. Show me what it looks like after....
-- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
Hi Don,
As per my email. There are around 60 consultants [but it can vary] and the once the macro has been replied it should look like this ? Consultant 1 Quarter To Date 1 2 3 4 Consultant 2 Quarter To Date 1 2 3 4 ... Thanks, Manir "Don Guillett" wrote: I'm getting old. Show me what it looks like after.... -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
If it looked like
c1 cp 1 2 3 4 qd 1 2 3 4 c2 cp 1 2 3 4 qd 1 2 3 4 and you want c1 qd 1 2 3 4 c2 qd 1 2 3 4 Try this after changing "cp" to your text, "h" to your column and 7 & 12 to suit Sub movitup1() For i = 7 To 12 If Cells(i, "h") = "cp" Then Cells(i + 1, "h").Resize(1, 5).Cut Cells(i, "h") Rows(i + 1).Delete End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi Don, As per my email. There are around 60 consultants [but it can vary] and the once the macro has been replied it should look like this ? Consultant 1 Quarter To Date 1 2 3 4 Consultant 2 Quarter To Date 1 2 3 4 ... Thanks, Manir "Don Guillett" wrote: I'm getting old. Show me what it looks like after.... -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
Hi Don,
Thanks for your solution. It works in deleting "Current Period" but not the "blank row which is created when "Quarter to Date " moves up one. I amended your code as follows. Sub movitup1() For i = 2 To 200 If Cells(i, 8) = "Current Period" Then Cells(i + 1, 8).Resize(1, 5).Cut Cells(i, 8) Rows(i + 1).Delete End If Next i End Sub Hi Don, As per my email. There are around 60 consultants [but it can vary] and the once the macro has been replied it should look like this ? Consultant 1 Quarter To Date 1 2 3 4 Consultant 2 Quarter To Date 1 2 3 4 ... Thanks, Manir "Don Guillett" wrote: I'm getting old. Show me what it looks like after.... -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
I don't see a difference?
Sub movitup1() For i = 7 To 12 If Cells(i, "h") = "cp" Then Cells(i + 1, "h").Resize(1, 5).Cut Cells(i, "h") Rows(i + 1).Delete' This line WAS there! End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi Don, Thanks for your solution. It works in deleting "Current Period" but not the "blank row which is created when "Quarter to Date " moves up one. I amended your code as follows. Sub movitup1() For i = 2 To 200 If Cells(i, 8) = "Current Period" Then Cells(i + 1, 8).Resize(1, 5).Cut Cells(i, 8) Rows(i + 1).Delete End If Next i End Sub Hi Don, As per my email. There are around 60 consultants [but it can vary] and the once the macro has been replied it should look like this ? Consultant 1 Quarter To Date 1 2 3 4 Consultant 2 Quarter To Date 1 2 3 4 ... Thanks, Manir "Don Guillett" wrote: I'm getting old. Show me what it looks like after.... -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code to Delete Part of Row with Data
Ok Don.
I will try again tomorrow at work. But if you think of anything please let me know. Thanks, Manir "Don Guillett" wrote: I don't see a difference? Sub movitup1() For i = 7 To 12 If Cells(i, "h") = "cp" Then Cells(i + 1, "h").Resize(1, 5).Cut Cells(i, "h") Rows(i + 1).Delete' This line WAS there! End If Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi Don, Thanks for your solution. It works in deleting "Current Period" but not the "blank row which is created when "Quarter to Date " moves up one. I amended your code as follows. Sub movitup1() For i = 2 To 200 If Cells(i, 8) = "Current Period" Then Cells(i + 1, 8).Resize(1, 5).Cut Cells(i, 8) Rows(i + 1).Delete End If Next i End Sub Hi Don, As per my email. There are around 60 consultants [but it can vary] and the once the macro has been replied it should look like this ? Consultant 1 Quarter To Date 1 2 3 4 Consultant 2 Quarter To Date 1 2 3 4 ... Thanks, Manir "Don Guillett" wrote: I'm getting old. Show me what it looks like after.... -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, Current Period which is text goes in Column H. The 1-4 numbers which are in columns J to M. "Quarter to Date" on the line below is also text with its own corresponding data in columns J to M. Once complete the "Quarter to Date" should be next to the consultant on thesame line Hope it makes sense .. "Don Guillett" wrote: I'm a bit confused about what goes where. How about a couple of examples of AFTER. You can use a findnext macro to locate the text along with a CUT procedure and a row.delete -- Don Guillett Microsoft MVP Excel SalesAid Software "manfareed" wrote in message ... Hi, I need a macro to delete part of a row eg. column H to Column M and in its place copy data into these columns from the row directly below it. Once done the row below should be "deleted". [please see below] The Data is arranged as follows : Cash Received Consultant 1 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 Consultant 2 Current Period 1 2 3 4 Quarter To Date 1 2 3 4 And so on ... I would need to delete from "Current Period" in Column H to Column M in all rows which have "Current Period" In its place I would need to copy "Quarter To Date" from the line below plus data in Columns J to M. Once Done the line with originally with "Quarter to Date" needs to be deleted so that everything moves up 1 row. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VB code required to compare/delete data | Excel Programming | |||
| How do I delete only part of the data in a row of cells? | Excel Worksheet Functions | |||
| DELETE SPACES In part | Excel Programming | |||
| Worksheet Change code not running when you just delete data in cells. | Excel Programming | |||
| Code and data part deux | Excel Programming | |||
Linear Mode
