Email:      Pass:    Pass?


Advertisements


Free Excel Forum

Insert Line Macro

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Does anyone know how to write a macro that will allow me to insert a row just
one row below the row with an "insert row" button on my worksheet. Basically
what I am trying to accomplish is to have a series of row categories where
information will be entered. If a user would like to add another line to a
category for an additional item, I want them to be able to click a button and
a blank line to be inserted above all the others in the category with the
same formatting as the other lines in the category. It would be great if
this was a generic macro that could be used for multiple categories on a
worksheet when assigned to each categories' separate button.

EAXAMPLE:

CATAGORY: CARS [Insert CARS line]
(After I click the "Insert CARS line" button, a new row with same formatting
as those below would be inserted here without negatively affecting the
ability of the "Insert CARS line" to complete the same functionality below.)
blue car 1999 good condition
green car 2000 poor condition
black car 2004 great condition

CATAGORY: TRUCKS [Insert TRUCKS line]
green truck 2001 poor condition
green truck 2001 good condition
black truck 2004 great condition

THANK YOU VERY MUCH FOR ANY INSIGHT!!!





Similar Excel Video Tutorials

Helpful Excel Macros

Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Enter Text/Characters with a Double-Click of the Mouse
- This macro allows you to enter any text or number, that you specify, in a cell on which you double-click. In the exampl
Create a Line Chart with a Macro in Excel
- Create a line chart in Excel with this macro. This allows you to quickly and easily create line charts and graphs from

Similar Topics







..Does anyone know how to write a macro that will allow me to insert a row just
one row below the row with an "insert row" button on my worksheet. Basically
what I am trying to accomplish is to have a series of row categories where
information will be entered. If a user would like to add another line to a
category for an additional item, I want them to be able to click a button and
a blank line to be inserted above all the others in the category with the
same formatting as the other lines in the category. It would be great if
this was a generic macro that could be used for multiple categories on a
worksheet when assigned to each categories' separate button.

EAXAMPLE:

CATAGORY: CARS [Insert CARS line]
(After I click the "Insert CARS line" button, a new row with same formatting
as those below would be inserted here without negatively affecting the
ability of the "Insert CARS line" to complete the same functionality below.)
blue car 1999 good condition
green car 2000 poor condition
black car 2004 great condition

CATAGORY: TRUCKS [Insert TRUCKS line]
green truck 2001 poor condition
green truck 2001 good condition
black truck 2004 great condition

..THANK YOU VERY MUCH FOR ANY INSIGHT!!!



Does anyone know how to write a macro that will allow me to insert a row just
one row below the row with an "insert row" button on my worksheet. Basically
what I am trying to accomplish is to have a series of row categories where
information will be entered. If a user would like to add another line to a
category for an additional item, I want them to be able to click a button and
a blank line to be inserted above all the others in the category with the
same formatting as the other lines in the category. It would be great if
this was a generic macro that could be used for multiple categories on a
worksheet when assigned to each categories' separate button.

EAXAMPLE:

CATAGORY: CARS [Insert CARS line]
(After I click the "Insert CARS line" button, a new row with same formatting
as those below would be inserted here without negatively affecting the
ability of the "Insert CARS line" to complete the same functionality below.)
blue car 1999 good condition
green car 2000 poor condition
black car 2004 great condition

CATAGORY: TRUCKS [Insert TRUCKS line]
green truck 2001 poor condition
green truck 2001 good condition
black truck 2004 great condition

THANK YOU VERY MUCH FOR ANY INSIGHT!!!




Is it possible to I set a macro on a command button that will insert a line BELOW the last line with data in it.

When the user click on the command button it inserts the line and keeps the format, macros and formulas as well as copying the radio button and drop down list, condition formatting.

The line is row 9 and range of A9 to AB9. I want all this line copied and pasted below the last line with data in it

Is this possible


Hi All,

I set up my spreadhseet so the "data entry" pages have nice conditional formatting giving new colors for each year. This was the start of my VBA quest...
2000
2000
2001
2001

Next I thought it would be a good idea to put a line between the different dates using:

Code:

Dim Rng As Range
Dim x As Long
Set Rng = Range("F2:F" & Range("F100").End(xlUp).Row)
For x = Rng.Rows.Count To 2 Step -1
       If Rng.Cells(x, 1).Offset(-1, 0).Value <> Rng.Cells(x, 1).Value Then
           Rng.Cells(x, 1).EntireRow.Insert Shift:=xlDown
           Rng.Cells(x, 1).EntireRow.Clear
        End If
Next x


The code worked exactly as advertised giving me:
2000
2000
Blank line - no color
2001
2001

BUT...the conditional formatting was absent on the rows which were inserted! So if I typed 2001 in the "Blank Line" it would not change the color as all the other 2001's and the actual condition rules changed to ignore the newly inserted line.

Does anyone have any thoughts on how I can correct this? I know I could write an entire macro to rearrange everything and make the right colors...But, that would be way overkill! If it is a simple(ish) fix I'd like to try, but this is the final cosmetic change to my spreadsheet, so it is not completely necessary.

Thank you!


Hi, I'm trying to insert a bit of code into my existing click button macro to insert a row based on a condition being met and then insert a formula into the resulting blank cell.

Range to look at = L1:L1000

Condition = "=IF(LEFT(R[-1]C,13)=""ORCSW FRM ***"",""insert row below here"",""go to next"")"

Code to insert into blank cell created in column L = "=""ORCSW HDW PNL ""&TEXT(R[-1]C[-3]*0.2,""00000.00"")&RIGHT(R[-1]C,4)"


Assuming this is easy enough to solve, I then want one final line of code to save the text in order in range L1:L1000 to a file called "labour00.txt", but only cells that have a do not equal "" or are not blank.

Thank you in advance for any help you can give.


I currently have a macro that, when a user clicks on a button, inserts a new line enabling the user to enter further information.

Now, the problem is that if I click the button to run the macro, or run the macro via the menu, a new line is inserted, but below the one the user is on.

If I go into VB and 'step' through the macro, then the line is inserted 'above' the one that the user is on which is what I want.

The macro I am using is;

Code:

Sub insert_newline()
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("A7").Select
End Sub


It's one of those things that I can't for the life of me work out what's going wrong where.


I have a lot of data and it's hard to find stuff among different categories.
I frequently sort and filter rows of data, so I need a dynamic solution.

I"m envisioning adding two buttons. One to enact the formatting below, and
another to remove the formatting below. Thanks for your help!

Starting at Row 13 and through Row 3000

Col A Col B Cols C through AS
Apples Little Entire row should be light green
Apples Little Entire row should be light green
Apples Big Entire row is blue, but insert line between big
and little row
Apples Big Entire row should be light green
Apples Big Entire row should be light green
Oranges Bad Entire row is now white, insert line for row between
apls orngs
Oranges Bad White Row
Oranges Good White Row, insert line
Pears Fine Entire row is now light green, insert line
etc..


Thanks... I may be getting ahead of myself because I have another thread on
a seperate subject to close, but I intend to work on these over the weekend...



I would like to have a button in my spreadsheet that when clicked will copy the current line, insert it and shift the line with the button down. I want to use this to dynamically expand a list that is being tracked, for example:
Colors
Red
Blue
Button reading "Insert new color"

When the button is clicked it would look like this:
Colors
Red
Blue
*blank cell*
Button reading "Insert new color"

The thing that is important is that I need the macro or whatever solution to copy the entire row that the button is on and insert that row shifting the row the button is on and the button down because there are formulas throughout the row that I need copied and inserted. The whole point is to give me the opportunity to click the button when needed to insert another row. Thanks in advance for the help.


I am trying to add lines to subcategories. Lets say there is
Dog
-pug
-retriever
-shitzu

Cat
-hairy
-smooth
-fiesty

Dog and Cat are in column e while the subs are in colum f
so it actually looks on the spreadsheet like
Dog Pug
Dog Retriever
Dog Shitzu
Cat Hairt
ETC

I want to be able to add a line to the last entry of each sub category. I am having a tough time figuring out the code as i havent worked with macros in excel. right now i have a line insertion macro, but it inserts it at the same place every time....meaning it starts inserting it in the middle of the grouping after a while. how can i have excel search for last entry of Dog in a column and then insert a line below this. I would need another button for cat...and then for my other categories. Here is my current code.

Rows("18:18").Select
Selection.Insert Shift:=xlDown
Range("F18").Select\

Any help would be much appreciated. i am trying to knock the socks off my bosses.

Caleb


Dunno if this is the correct title for this problem
I have a sheet where i enter data, at the end of each sheet in a hidden cell i have the follwoign code

Code:

=IF(O5="y",MAX(S$1:$S1)+1,0)


Which is copied down for the length of the data sheet. This code if for extracting data to another sheet.

On the data sheet the user has the option of inserting a new line, due to formula contained in the rows, When the user selects insert from a custom menu a new line is inserted into the data, this line is stored on another sheet.

Now i have the above formula inserted into the master line

The formula copies over but the exsiting formula does not update

example

line 1 =IF(O5="y",MAX(S$1:$S1)+1,0)
line 2 =IF(O5="y",MAX(S$1:$S2)+1,0)
line 3 =IF(O5="y",MAX(S$1:$S3)+1,0)
line 4 =IF(O5="y",MAX(S$1:$S4)+1,0)
line 5 =IF(O5="y",MAX(S$1:$S5)+1,0)

when i insert a line between 2 and 3 the following happens

line 1 =IF(O5="y",MAX(S$1:$S1)+1,0)
line 2 =IF(O5="y",MAX(S$1:$S2)+1,0)
line 3 =IF(O5="y",MAX(S$1:$S3)+1,0) <--- new Line
line 4 =IF(O5="y",MAX(S$1:$S3)+1,0)
line 5 =IF(O5="y",MAX(S$1:$S5)+1,0)
line 6 =IF(O5="y",MAX(S$1:$S6)+1,0)

As you can see line 3 and 4 are showing the same formula

What needs to happen is that the formula re alines it self so line 4 should show =IF(O5="y",MAX(S$1:$S4)+1,0) , line 5 =IF(O5="y",MAX(S$1:$S5)+1,0) and so on

Hope that explains what is happening

G


Hi guys

Please find attached two spreadsheets. The first one "Sample" is the spreadsheet that my client used to use earlier. The second one "Project" that I am making to replace the old one.

Now in "Sample" workbook, in the worksheet "PCs and scanners" when the user click on New scannerline button on the worksheet then a new line gets inserted in the scanning section only.

I want to do the same thing in the workbook "Project" like in the userform when the user click on Enter button then it should check if the "new scanner line" textbox is empty and if yes then no line should be inserted in scanning section other wise a line should be inserted as in workbook "Sample".

Thanks


I created a chart containing several lines and based on a condition I want to insert a vertical line into the chart.
The condition should be something like
if (match($C$5,K5:K704,0), <insert vertical line>)

how can achieve that?


I have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.

How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.


Code:

Sub Hide_Rows_Estimates_IBM()
 Dim R As Long
 Dim Rng As Range
 
   Set Rng = Worksheets("IBM Estimate").Range("G25:G107")
   
     For R = Rng.Rows.Count To 1 Step -1
       If Rng.Cells(R, 1) = 0 Then Rng.Cells(R, 1).EntireRow.Hidden = True
     Next R
End Sub





I have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.

How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.


Code:

Sub Hide_Rows_Estimates_IBM()
 Dim R As Long
 Dim Rng As Range
 
   Set Rng = Worksheets("IBM Estimate").Range("G25:G107")
   
     For R = Rng.Rows.Count To 1 Step -1
       If Rng.Cells(R, 1) = 0 Then Rng.Cells(R, 1).EntireRow.Hidden = True
     Next R
End Sub





I was wondering if it is possible to insert a line without a direct cell
reference.
This is exactly what I am trying to do...

I have import files that have many different lines...(each one is unique).
I wrote a macro to go thru the many tedious steps, except I am stuck on the
line insertion part....I do a data sort for column "C", and then I do a find
for "X" (which is a separation point). What I want to happen is for the
macro to find "X" and insert a line above it, regardless of which row "X" is
on. Is there a way to do that?

Thanks!!!



Hi,

Need help by anyone, I need help creating a macro that would insert a row, if a condition is met. Pls see image. This is the initial table.

Table1.jpg

I need a macro that will insert a row IF the "Component Type" is equal to "E-Cap" and "FET" just like this:

Table2.jpg

I hope that someone could help me on this! Thanks!

I am just learning Excel is there a way to have a line insert automatically when a condition is met?

For example if I have column that has account numbers entered it, I would like to have a row inserted when the account number changes.

Acct #
225
225
225
226 Since this changes I want a line inserted??

Thanks if you can help!!


Hi

I am creating a simple excel sheet to keep track of book requests and costs and need to create a macro which will insert a new line at the bottom of the table and also copy things such as cell formatting and validation. As the table grows it needs to keep adding a line at the bottom of the table each time the macro is run. The problem is that I can only get a macro to insert the line at a specific point for example line 3 and as the table expands that no longer is at the bottom of the table.
Can someone help me please? Thanks

An example is shown below

Department l Name l Order Number l Cost
-----------------------------------------------
Design l Mr.Smith l O76868686 l 17.99
Design l Mr.Jones l O76868687 l 27.99
[Need to insert line here]

BUTTON FOR INSERT LINE MACRO


I have an Excel sheet with more than 40,000 lines and goes to Col BW. Cols M and N have data that are either in Black or Green both of which are derived by conditional formatting. Some cell in Col M are blank. No blank cells in Col N. If necessary I can fill all blank cells in Col M with a color letter (color other than black or green). Data starts on line 3.

Lines might look like this for

Line 3 Col M: t1/8 and Col N 3/16
Line 4 Col M: 6 and Col N t16
Line 5 Col M: 16 and Col N 1/8
Line 6 Col M: t16 and Col N t16

I need a Macro that will delete all lines that have Black /Black (line 5) and Green/Green (line 6)

Leaving me with Green/Black (line 3) and Black/Green (line 4)

Thank you

Ironsides


Hi,

I am trying to set up a spreadsheet so that one of the charts (specifically, a pie chart) is only filled with values if a condition is satisfied. The condition is a simple one, either a certain cell equals "Yes" or "No", in cell D1. The pie chart contents are in cells A1:B10.

However I'm having trouble working out how to create a dynamic range for my chart data such that the chart is empty if the condition is "No". I've tried creating Series and Category dynamic ranges like this

Series - =IF($D$1="Yes", $B$1:$B$10, EMPTY())
Category - =IF($D$1="Yes", $A$1:$A$10, EMPTY())

However this doesn't give the required result... it throws up an 'invalid references' error whenever D1 = "No", and doesn't remove the (blank) legend from the chart.

Ideally what I'm looking for is for when D1 = "No", to have the chart look like a chart does when you first click 'Insert Chart', i.e. completely blank. Any ideas on this? I've got a feeling it might be impossible.


I'm tracking my sales. One column (I) has a dropdown for either "New" or "Used" and the other (J) has a dropdown for either "Car" or Truck.

New Car
New Car
Used Truck
Used Car
New Truck
Used Truck
Used Car

How can I make Excel Count the number of "Car" that are ALSO "New" I've tried some COUNTIF commands with an AND command in there. I may be way off, but I also could just have a ) in the wrong place.

I need it to tell me that {New Cars=2}{New Trucks=1}..etc...

I hope I explained this ? alright.

Thanks for all your help.


Hi there,

I have asked this question before but was hoping I might be able to achieve
this by using a formula without a macro being involved!

I have a spreadsheet made up of 5 worksheets and I would like to insert a
line of text on the worksheet called Percentages if a specific condition is
met on another worksheet called Other.

For example on Other I currently have a Data Validation box in E34 where I
can choose Y, N or N/A and depending upon the result enters a 1 in the
adjacent cell F34 or leaves it blank.

That current formula on Other is =IF(E34="Y",1,"") which works as expected.

I would however like to insert a line of text 'Training Issue' into the
Percentages worksheet at say I41 if Y is selected in Other E34

I have tried entering the following formula in Percentages I41 but it
doesn't work - obviously my logic is flawed.

=IF(Other!E34=0,"",(IF(Other!E34=Y,"Training Issue"))

That is the best I can come up with and it is obviously wrong so can some
kind soul help please?

--
Thanks in advance & regards,
-=pp=-





Hi,
I want to put a button on the last row of a range for the user to use to insert a line after the last row of that range (I do not want the user to right click on the row and select insert) The button must stay on the last row when lines are inserted.There will always be data beneath the row that would be inserted.
I would appreciate your help.
Wikus


I have a table with 200 rows. Each row further has 25 categories, each category has a max & min value. Each row & category is a condition to check to return True or False in a VB procedure. Each condition in turn has to be applied to a set of 5000 records for validation of data. Instead of typing each condition in the VB editor screen, I need a code that will take up the values from the table in excel as one procedure or even 25 procedures will do. The table looks like this -


Category 1 (condition 1) (min)0.00 (max)50.00
Category 1 (condition 2) (min)0.00 (max)10.00
Category 2 (condition 1) (min)0.10 (max)10.00
Category 2 (condition 2) (min)1.00 (max)80.00


Regards,


Is there a quick solution for inserting a new line using a command button.

I'm standing on different lines in the excel sheet. When pressing the command button on top of document, I can insert line under the active cell. I know i can use a keyboard shortcut, but I will populate this line with other information as well (that I have figured) Recording the macro gives me absolute values: Range ("A6").Select I would like something like this: Range.activecell.select, insert row beneath this row.

any suggestions?