Help with Report

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
I have an excel sheet with a list of data in rows 3-10, and row 11 being the totals row. Everytime I insert a row, I have to adjust my totals formulas to account for the inserted row. Is there a way totals to avoid adjusting my formulas?
and..
How do I write a macro to insert a row above the TOTALS row, and then copy and paste into the inserted row? I should be able to figure out the copy and paste part, but have no idea on how to find the totals row and insert a row above the TOTALS row....

any help would be appreciated.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
The easiest way is to put your totals on row 12, including the blank row 11 just above it in the SUM. Then adjust the row height of row 11 to 1 or 2, so you don't type anything into it. You can put a border on these cells, if you want.

Then you can insert rows to your heart's content. And you've managed to avoid an unnecessary macro!
 

help_questions

Board Regular
Joined
Aug 22, 2005
Messages
215
I tested the blank row tip..this works great.

What I need now is a marco that will add a row above this blank row and copy data into the new row.

Or a macro that will find the greatest date in column A and then insert a row below the greatest date and insert values into this new row...
 

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
Here's a macro that searches for the "blank" row on the assumption that it is set to a row height of 2. It then captures the data from a row above and copies it into the new row, then returns to the original selection.

Note that you will have to adjust the items in the Assumptions section.

Sub OpenNewRow()
'Go to 'blank' row and insert new row
Dim sCurrentSelection As String
Dim sngRowHeight As Single
Dim sLeftColumn As String
Dim sRightColumn As String

'Assumptions:
'Blank row has height of 2
sngRowHeight = 2
'Left column is at A
sLeftColumn = "A"
'Right column is at J
sRightColumn = "J"

'Remember where we were at start
sCurrentSelection = Selection.Address

FindBlankRow:
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
If ActiveCell.RowHeight > sngRowHeight Then GoTo FindBlankRow

Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Range("A1").Select 'Move up
ActiveSheet.Range(sLeftColumn & ActiveCell.Row & ":" & sRightColumn & ActiveCell.Row).Select
Selection.Copy
ActiveSheet.Range(sLeftColumn & ActiveCell.Row + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Return to original selection
ActiveSheet.Range(sCurrentSelection).Select
End Sub
 

Forum statistics

Threads
1,144,389
Messages
5,724,073
Members
422,534
Latest member
ThiccNugg

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
Top