Creating a new blank row and moving data to that row

BigEInMT

New Member
Joined
Jun 24, 2019
Messages
11
I am currently configuring a financial report which lists description data as well as financial data. I will post a copy of the layout below. What I'm looking to do, is to use a macro/vba or whatever I need to in order to allow me to create a blank row above any row which contains the word Totals. I'd then like to move the data from Column 15 and paste the data into Column A of the new blank row. I'd also like to Merge the cells from the first column through Column 8 for the new blank row but that may be asking too much. I'm going to post what the report looks like now and what ultimately I would like the report to look like after the changes are made.

Now:
Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9Column 10Column 11Column 12Column 13Column 14Column 15
Number: 1
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00Description
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Totals$0.00$0.00$0.00$0.00$0.00$0.00
Number: 2
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00Description
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Totals$0.00$0.00$0.00$0.00$0.00$0.00

<tbody>
</tbody>


What I'd like it to look like:
Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9Column 10Column 11Column 12Column 13Column 14Column 15
Number: 1
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Description
Totals$0.00$0.00$0.00$0.00$0.00$0.00
Number: 2
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Description
Totals$0.00$0.00$0.00$0.00$0.00$0.00

<tbody>
</tbody>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Re: Help creating a new blank row and moving data to that row

Try this:

Code:
Sub InsertAdj()
Dim LR As Long, i As Integer, n As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 2 Step -1
  If Range("A" & i) = "Totals" Then
  Rows(i).Insert Shift:=xlDown
  n = WorksheetFunction.Match(WorksheetFunction.Lookup("zzzzz", Range("A1:A" & i)), Range("A1:A" & i), 0)
  Cells(i, 1) = Cells(n + 1, 16).Value
  Cells(n + 1, 16).ClearContents
  End If
End Sub
Next i
 
Last edited:
Upvote 0
Re: Help creating a new blank row and moving data to that row

Unfortunately, I get a "Compile error: For without Next" when I try to run that code in VBA.
 
Upvote 0
Re: Help creating a new blank row and moving data to that row

I moved the Next i line of code above the End Sub and there were no errors. I ran the macro but nothing happened within my spreadsheet.
 
Upvote 0
Re: Help creating a new blank row and moving data to that row

You're right...it was a sloppy copy issue w/the macro. Sorry about that.

But, I just reran the macro and it did exactly what you described.

Head scratch. I have your data beginning in A1 which is the assumption the macro makes.
A1 in my case w/your data reads "Number: 1"

Before the macro is run, the last row in the data (from your example) is A10 with "Totals" in that cell and the totals from J10:O10.
The two descriptions are in P2 and P6, respectively.

Is that what you have?

Number: 1
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00This is the first description
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Totals$0.00$0.00$0.00$0.00$0.00$0.00
Number: 2
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00Here is the second description
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Totals$0.00$0.00$0.00$0.00$0.00$0.00

<tbody>
</tbody>


After running the macro:

Number: 1
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
This is the first description
Totals $0.00$0.00$0.00$0.00$0.00$0.00
Number: 2
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
DataDataDataDataDataDataDataData$0.00$0.00$0.00$0.00$0.00$0.00
Here is the second description
Totals $0.00$0.00$0.00$0.00$0.00$0.00

<colgroup><col span="15"><col></colgroup><tbody>
</tbody>
 
Last edited:
Upvote 0
Re: Help creating a new blank row and moving data to that row

I apologize as I left out the fact that there is a report header involved within the report so the rows begin at A5 and go to P5. The number of groups, shown by Number: 1 and Number: 2, could range from 1 group to 100 groups with varying amounts of rows within said group.

In my example above, A5 is blank and then Column 1 starts in B5 and Column 15 is in P5. The first Totals row is in A9 and the second Totals row is in A15. Like I mentioned though, this process could be continued 100 times over and have 100 Totals rows. The first grouping should always start in Row 5 for this report, which would be below the header.
 
Upvote 0
Re: Help creating a new blank row and moving data to that row

Change the "for" line in my macro:

Code:
For i = LR To 5 Step -1
 
Upvote 0
Re: Help creating a new blank row and moving data to that row

What would I need to enter if I want it to look for any row which contains "Totals" as the row may say Totals for blah blah blah?
 
Upvote 0
Re: Help creating a new blank row and moving data to that row

You want the word "Totals" to exist by itself and nothing else in the row or just a specific column?
 
Upvote 0
Re: Help creating a new blank row and moving data to that row

The "Totals" column states "Totals for "claim number" - "number of events"". I realized I didn't also clarify that the "Totals" row doesn't just say "Totals". I was just keeping the row generic but I didn't clarify that and should have. This is just a generic example but using my example above the "Totals" row shows as Totals for Number 2 - 4 Events.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top