Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Delete Part of Row and move next Row Down Up

  Asked By: Kerri    Date: Oct 27    Category: MS Office    Views: 2112
  

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]

There are around 60 consultants [but it can vary].

The Data is arranged as follows from row 3 onwards.



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 ...
[please note numbers 1-4 represent numerical values]

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.

Once the macro has been appplied it should look like this ?

Consultant 1 Quarter To Date 1 2 3 4
Consultant 2 Quarter To Date 1 2 3 4

Share: 

 

11 Answers Found

 
Answer #1    Answered By: Davi Costa     Answered On: Oct 27

Record a macro  of yourself doing it. You can then work it into the rest of
your code.

 
Answer #2    Answered By: Sydney Thompson     Answered On: Oct 27

Thanks ...but I was looking for something a bit more
dynamic.

 
Answer #3    Answered By: Kim Cruz     Answered On: Oct 27

Sub Macro1()
Dim MT As Integer
Range("H3:M3").Select
Selection.Delete Shift:=xlUp
Range("B4").Activate
MT = 0
Do While MT < 10
If ActiveCell.Value = "Quarter To Date" Then
ActiveCell.EntireRow.Delete
MT = 0
ActiveCell.Offset(-1, 0).Activate
ElseIf Len(ActiveCell.Value) = 0 Then
ActiveCell.EntireRow.ClearContents
MT = MT + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

 
Answer #4    Answered By: Adelbert Fischer     Answered On: Oct 27

I forgot you wanted the rows  to have a "Quarter To Date" heading instead
of "Current Period" when finished. This modified version of my earlier
suggestion will do that, too.

Sub Macro1()
Dim MT As Integer
Range("H3:M3").Select
Selection.Delete Shift:=xlUp
Range("B4").Activate
MT = 0
Do While MT < 10
If ActiveCell.Value = "Quarter To Date" Then
ActiveCell.EntireRow.Delete
MT = 0
ActiveCell.Offset(-1, 0).Activate
ElseIf Len(ActiveCell.Value) = 0 Then
ActiveCell.EntireRow.ClearContents
MT = MT + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
Columns("B:B").Select
Selection.Replace What:="Current Period", Replacement:="Quarter To Date", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=
_
False, ReplaceFormat:=False
Range("A1").Select
End Sub

 
Answer #5    Answered By: Bian Nguyen     Answered On: Oct 27

Thanks for the code. I will test it and let you know.

 
Answer #6    Answered By: Daniel Jones     Answered On: Oct 27

Doesn't quite work. The "Consultant" and "Quarter to
Date" should always be on the same line.

What is "MT" in the code below.

 
Answer #7    Answered By: Mercedes Andrews     Answered On: Oct 27

Your spreadsheet must be different than what I understood from your original
email. To create dummy data  for developing the macro, I entered Consultant1 in
A3 on a blank sheet. I entered Current Period in B3 and Quarter To Date in B4.
Then I selected A3:B5 and used the fill handle to copy  down through row  32,
giving me headings for Consultant1 through Consultant10. I put even numbers  in
the Current Period rows  and odd numbers in the Quarter To Date rows. Does that
resemble your spreadsheet? With or without the blank row between sets of
records, the macro  gives me exactly the output you described.

MT is a counter. When it accumulates a count of 10 contiguous empty cells in
column B, the macro stops looping. Since you said the number of consultants may
vary, this is a way for the macro to know when to quit.

 
Answer #8    Answered By: Fuzairah Neeman     Answered On: Oct 27

Thanks for your reply. The consultant names are in
column 1 and the "Quarter to Date" is in column  H.
I will have another look at your code.

Since the info is downloaded from Crystal there are
some blank rows.

 
Answer #9    Answered By: Brandi Ramirez     Answered On: Oct 27

Yes, if the Current Period & Quarter To Date headings are in column  H, the macro
won't work as intended. Here is a revised version:

Sub Macro2()
Dim MT As Integer
Range("I3:M3").Select
Selection.Delete Shift:=xlUp
Range("H4").Activate
MT = 0
Do While MT < 10
If ActiveCell.Value = "Quarter To Date" Then
ActiveCell.EntireRow.Delete
MT = 0
ActiveCell.Offset(-1, 0).Activate
ElseIf Len(ActiveCell.Value) = 0 Then
ActiveCell.EntireRow.ClearContents
MT = MT + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
Columns("H:H").Select
Selection.Replace What:="Current Period", Replacement:="Quarter To Date", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=
_
False, ReplaceFormat:=False
Range("A1").Select
End Sub

 
Answer #10    Answered By: Archie Parker     Answered On: Oct 27

I think I have figured it out ................

 
Answer #11    Answered By: Hamdan Younis     Answered On: Oct 27

Yes, but once you know HOW to do it, you can insert it in the rest of your
code.

 
Didn't find what you were looking for? Find more on Delete Part of Row and move next Row Down Up Or get search suggestion and latest updates.




Tagged: