VBA Code to Delete Part of Row with Data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

manfareed - 21 Sep 2007 11:36 GMT

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 to this Message

Symbiosis - 21 Sep 2007 11:58 GMT

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

> Hi,
> I need a macro to delete part of a row eg. column H to Column M and in its
[quoted text clipped - 21 lines]
> Once Done the line with originally with "Quarter to Date" needs to be
> deleted so that everything moves up 1 row.

Reply to this Message

manfareed - 21 Sep 2007 16:48 GMT

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

> Hi..
>
[quoted text clipped - 55 lines]
> > Once Done the line with originally with "Quarter to Date" needs to be
> > deleted so that everything moves up 1 row.

Reply to this Message

Don Guillett - 21 Sep 2007 13:38 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

Reply to this Message

manfareed - 21 Sep 2007 16:52 GMT

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

> I'm a bit confused about what goes where. How about a couple of examples of
> AFTER.
[quoted text clipped - 25 lines]
> > Once Done the line with originally with "Quarter to Date" needs to be
> > deleted so that everything moves up 1 row.

Reply to this Message

Don Guillett - 21 Sep 2007 20:52 GMT

I'm getting old. Show me what it looks like after....

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 44 lines]
>> > Once Done the line with originally with "Quarter to Date" needs to be
>> > deleted so that everything moves up 1 row.

Reply to this Message

manfareed - 22 Sep 2007 17:40 GMT

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

> I'm getting old. Show me what it looks like after....
>
[quoted text clipped - 46 lines]
> >> > Once Done the line with originally with "Quarter to Date" needs to be
> >> > deleted so that everything moves up 1 row.

Reply to this Message

Don Guillett - 22 Sep 2007 19:48 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi Don,
> As per my email. There are around 60 consultants [but it can vary] and the
[quoted text clipped - 65 lines]
>> >> > be
>> >> > deleted so that everything moves up 1 row.

Reply to this Message

manfareed - 23 Sep 2007 20:46 GMT

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
[quoted text clipped - 65 lines]
> >> >> > be
> >> >> > deleted so that everything moves up 1 row.

Reply to this Message

Don Guillett - 23 Sep 2007 20:59 GMT

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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi Don,
>
[quoted text clipped - 87 lines]
>> >> >> > be
>> >> >> > deleted so that everything moves up 1 row.

Reply to this Message

manfareed - 23 Sep 2007 21:32 GMT

Ok Don.

I will try again tomorrow at work. But if you think of anything please let
me know.

Thanks,

Manir

> I don't see a difference?
>
[quoted text clipped - 98 lines]
> >> >> >> > be
> >> >> >> > deleted so that everything moves up 1 row.

Reply to this Message