| 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. |
|
#3
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. |
| 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 | |||
Hybrid Mode
