VBA Code to Delete Part of Row with Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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
(e-mail address removed)
manfareed said:
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.
Click to expand...
Click to expand...
 
I'm getting old. Show me what it looks like after....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
manfareed said:
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 said:
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
(e-mail address removed)
manfareed said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
I'm getting old. Show me what it looks like after....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
manfareed said:
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 said:
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
(e-mail address removed)
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.
Click to expand...
Click to expand...
Click to expand...
 
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
(e-mail address removed)
manfareed said:
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 said:
I'm getting old. Show me what it looks like after....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
manfareed said:
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.
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
(e-mail address removed)
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
I'm getting old. Show me what it looks like after....

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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.
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
(e-mail address removed)
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.
Click to expand...
Click to expand...
Click to expand...
 
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
(e-mail address removed)
manfareed said:
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

:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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.
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
(e-mail address removed)
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.
Click to expand...
Click to expand...
Click to expand...
 
Ok Don.

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

Thanks,

Manir

Don Guillett said:
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
(e-mail address removed)
manfareed said:
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

:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
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.
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
(e-mail address removed)
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top