Free Microsoft Excel 2013 Quick Reference

Auto Number After Inserting Row

Hi guys, anybody can help me or giving me some idea on how to solve my problem.
I'm trying to make a table with autonumbering even if i insert a new row it will automatically add numbering according to row number. i tried it using IF function but after inserting row i have to drag the formula down again. please see attach. thanks


Post your answer or comment

comments powered by Disqus
On several worksheets someone can insert rows as needed, which means there will be a blank somewhere in column A, and the autonumber feature is interrupted. I have one piece of public code in the general module, which will renumber column A, starting with cell A7. This works fine, and in fact, I have it attached to a button on the worksheet, so that if someone inserts a row, then the person can click on this "Renumber" button, and this code works great.

	VB:
	
 Renumber() 
    Dim myRng As Range 
    Dim j As Integer, i As Integer 
    Application.ScreenUpdating = False 
    j = Range("A7:A" & Range("A65356").End(xlUp).Row).Rows.Count 
     
    For i = 7 To j 
        Cells(i, 1).FormulaR1C1 = _ 
        "=IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="""",1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)" 
    Next i 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I am dealing with people who have no knowledge of code, little knowledge of Excel, and don't understand why something has to require two steps (insert rows, then click the button). So I have been trying to provide a Private Sub that would detect whether there has been an empty cell in the range A7 to last row, and if so, then call the Renumber Sub. It seems like a simple thing to do, but the longer I play with it, the more complicated I am making this. This is my latest attempt, in which I call the Public Sub Renumber. But...


	VB:
	
 Range) 
    Private Sub Worksheet_Change(ByVal Target As Range) 
        Dim VRange As Range 
        Set VRange = Range("A7:A" & Range("A65356").End(xlUp).Row) 
        If Not Intersect(Target, VRange) Is Nothing Then _ 
        Call Module1.Renumber 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...it seems that renumbering triggers the code, which triggers the code, which.... In other words, once it tries to renumber, the code cycles indefinitely.

Is there a way to prevent such recycling through the code?

After I inserted rows in a workbook in Excel 2003 that was a source workbook
for another Excel 2003 workbook, the formulas containing the links in the
destination workbook did not function correctly. I understand that this is
because the links no longer contained the correct cell numbers from the
source document, but I am wondering whether there is a way to prevent this
from happening, or to fix it quickly, without having to go in and change the
cell numbers in each formula of each link in the destination workbook.

I would appreciate any help in streamlining this process.

Thank you,

Peter Turpin
--
Peter Turpin
Cultural Liaison

I have a large Excel file, and at times, following moving rows from one area to another (cut, insert rows), the file gets corrupted. The only way around this at times is to insert a number of blank rows, then cut and paste (not insert rows). There is no indication that things are bad until you try to reopen the file.

Has anyone run into this problem, and is there a recommened fix of any kind?

Thanks.

Hi,
I have a filtered column A, and I want to add a new column B by inserting auto-number(e.g. A01,A02...).

Is it possible?

Thanks

George

So I have this macro and it inserts rows after the last one with data in column A. It correctly copies all of the data validation as I need from the row above. However, I am trying to work it in to copy the formulas from above also so I think I am going about this the wrong way..... but I am having a brain fart and am not figuring out the right way to do this. I have tried a handful of ways to do this and am failing... I know it is something sooo easy too. Technically it is just column G. I just need it to copy the formula from the row after the last row from column G and past it into the row it just inserted which would now be the row after the last row with data.

Sub InsertSheetsAndSort()

Dim cnt As Integer
Dim pwrd As String
Dim wks As Worksheet
Dim LastRow As Long
Dim myRng As Range
pwrd = "XXXX"
cnt = 0
Set wks = ActiveSheet

With wks
   LastRow = .Range("A3").End(xlDown).Row
   Set myRng = .Range("A3:L" & LastRow)
End With

CntWanted = 0
CntWanted = CntWanted + InputBox("How many rows would you like to add?")
If CntWanted <= 100 And CntWanted > 0 Then

ActiveSheet.Unprotect pwrd

Do
    wks.Rows(LastRow + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove
    cnt = cnt + 1
Loop Until cnt = CntWanted

With myRng
   .Cells.Sort _
        Key1:=.Columns(5), Order1:=xlAscending, _
        key2:=.Columns(3), order2:=xlDescending, _
        Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom
End With

ActiveSheet.Protect pwrd

Else
MsgBox ("Please enter less than 100 and more than 0.")

End If

End Sub


Issue Number 1: I have a task list that I am trying to set up an auto number VBA code for. I have a header row in my work sheet and i would like that to be excluded from the count or auto number. I have already figured out how to do auto date and time entry but I am struggling with auto number. Can someone please help? I have pasted the code I currently have below as well as a listing of the column headers for reference.

Row 1 - Headings
Column A - ID# (Auto)
Column B - Date (Auto)
Column C - Time (Auto)
Column D - Issue (Manual Entry)
Column E - Responsible Party (Manual Entry)
Column F - Actions Taken (Manual Entry)
Column G - Status (Dropdown List)
Column H - Cost (Manual Entry)

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

        If Not Intersect(Target, Range("D2:D1000")) Is Nothing Then

            With Target(1, -1)

                .Value = Date

                .EntireColumn.AutoFit

            End With
            With Target(1, 0)

                .Value = Time

                .EntireColumn.AutoFit

            End With
            
        End If
        
        End Sub
Issue Number 2: I currently have the Status column set up with conditional formatting so that the 3 different options appear as different colors (Open=Red, In Progress=Yellow and Closed=Black). This however only changes the color the single cell of column G. I would like to have it color the ENTIRE row and change when the value in column G changes. Is this possible? If so how?

Thanks in advance

Good morning everyone,

I need to run a formula/macro/something that will check all the numbers in a column and either highlight when a number is out of sequence and/or insert a blank row where there are missing numbers. The inserting rows would just be a luxury for me so I don't have to go back and do it manually. Reason for the blank rows is I have to put all the info on my spreadsheet back into the customer's spreadsheet and instead of hiding rows we weren't going to quote, someone deleted them and I don't really want to cut and paste 9000 rows manually.

I searched the forum and found a couple conditional formatting solutions, which didn't work (which could be user error because I hadn't used conditional formatting in Excel 2007 yet). Other suggestions were helper columns and an add-in, neither of which work as needed.

Thanks in advance!

Hey everyone, good to be part of the forum, I'm learning a lot.

I have tried searching the forum for my question and I have received a few answers, but I still do not understand. What I want to do is counta the amount of data I have on one page and then insert that many rows on another page. I then want the inserted rows to populate with a vlookup equation so that they can grab select data from the data page. I also want this to automatically update. If I have 10 pieces of data and delete one, I want the number of inserted rows to shrink to 9. Any help with this problem would be great!

Attached is a spreadsheet with an example of what I want to do.

How can I auto number an inserted column by how many records are in another column in VBS?

Thanks

I have a spread sheet that I have locked with the expection of certain cells. I also have left the ability to insert and delete rows. The cells that are locked have functions in them run in sequance.
Is there a way that when the new row is inserted its copies the formula from the row above automatically but also adjust for the new row and adjusts the rows below it automatically?

For example if I insert a new row between rows 2 and 3 below.
(orginal layout)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (locked)(function is =sum(a3:b3)
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)

(layout after inserting row)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (unlocked) *inserted row*
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
a5 (unlocked) b5 (locked)(function is =sum(a5:b5)

this is what i want the end product to be.
(layout if formulas are copied and adjusted after inserting row)
a1 (unlocked) b1 (locked)(function is =sum(a1:b1)
a2 (unlocked) b2 (locked)(function is =sum(a2:b2)
a3 (unlocked) b3 (locked)(function is =sum(a3:b3) *inserted row*
a4 (unlocked) b4 (locked)(function is =sum(a4:b4)
a5 (unlocked) b5 (locked)(function is =sum(a5:b5)

Is this possible by any means?

Any one please help me.......?

After inserting row how can I go back to the row I selected for inserting,
instead for going to A1

Sub insertrowatselection()
Rows(ActiveCell.Row).Insert
Range("A1").Select

On a very large spreadsheet, one with validation and referencing lists, i
want to insert additional rows in the top 9 rows. Below those 9, when
rclicking on the row number, the Insert Rows option is black/available. But
not for the top 9 rows. There it's greyed out. This is killing me. What's
up?!

Hello,

I'm using a simple auto numbering command.

eg: cell A1: 1
cell A2: =(A1+1)

then shift, copy/paste cell A2 until A600,

This creates a sequential number column from 1-600. My problem lies when a new row is inserted, which is left blank. How can i maintain this auto function upon a new Row insertion?

Thanks

Jeff

hi All,

i have an excel file containing hundreds of rows and columns. one column is having different dates for the next 10 years.
Daily i have to manually insert 3 rows between every change in date.
Somebody please help in writing a macro to auto insert rows after every change in date (Dates are in column).

Your help will really change my hours and years.

looking forward for your kind help

bye and cheers

Anil kumar

I am working with a large list of numbers (between 0000 and 9999). The
list has breaks in it that I need to fill without disturbing the data
assigned to specific numbers. My question is if there is a way to get
excel to automatically insert rows in this list to fill these gaps.
This would save me many hours of work. Thank you in advance! Example
below.

Befo (what I have)

0001 ABC
0005 DEF
0006 GHI
0007 KLM
00011 NOP

After: (what I need)

0001 ABC
0002
0003
0004
0005 DEF
0006 GHI
0007 KLM
0008
0009
0010
00011 NOP

Joe Z

--
zdek
------------------------------------------------------------------------
zdek's Profile: http://www.excelforum.com/member.php...o&userid=35510
View this thread: http://www.excelforum.com/showthread...hreadid=552822

I am working with a large list of numbers (between 0000 and 9999). The list has breaks in it that I need to fill without disturbing the data assigned to specific numbers. My question is if there is a way to get excel to automatically insert rows in this list to fill these gaps. This would save me many hours of work. Thank you in advance! Example below.

Before: (what I have)

0001 ABC
0005 DEF
0006 GHI
0007 KLM
00011 NOP

After: (what I need)

0001 ABC
0002
0003
0004
0005 DEF
0006 GHI
0007 KLM
0008
0009
0010
00011 NOP

Joe Z

We recently bought some new software at work, and I'm having trouble formatting the exported data in a way that will work with existing reports. The attached file is what the data looks like after being exported. Basically, what I'm trying to do is insert rows for the missing numbers -- i.e., each name should be followed by rows numbered 1 through 10.

John Smith John Smith
1 1
4 2
8 3
4
5
6
7
8
9
10

My ultimate end goal is to have columns of data for each name, instead of rows:

John Smith 1 2 3 4 5 6 7 8 9 10

I've created a formula that can do that part, but it operates on the assumption that each name will be followed by a set number of rows.

What little I know about vba I've learned on my own, so I may just be missing something obvious. Any help would greatly appreciate!

Hi,
I have a .txt file that contains invoice information and I need to import the file in Excel do some manipulations and insert a page break after 30 rows of related invoice data. For example; I have in column 8 the number of lines of data for each invoice number in column 3. I can get page breaks inserted every 30 rows but how do I get rid of the automatic page breaks that excel puts in? I have one invoice with 44 rows of data and I put in a page break at row 57 but excel also adds a page break at row 52.

thanks for any help you can give

Hey Guys

I have a program with a macro that inserts a new row with values from B3:H3. After awhile, the sheet starts to get crowded like crazy, so I only want to see the first 25 entries (Rows 3 -> 27), after which I would like for it auto-hide any other rows THAT HAVE data in them. So everytime I add new data, the new data will be at the top in row 3, and the value that was in row 27 (now row 28) auto hides.

I would also like for these to be already hidden everytime I start up the application.

Any ideas?
Thanks!

Hi guys,

Please help a guy in need out!

I basically need a way of auto inserting two blank rows after two rows of data.

ie I have:

1
1
(blank)
(blank)
2
2
(blank)
(blank)

This is for about 500+

Please help!

Thanks in advance.

Hello,

I have multiple sheets, each named after the corresponding month. On each
row, I have an account number, with a list of different data listed across in
the same row. What I am trying to do is have certain cells refer to other
cells on a different sheet. For example, November!c5 would get the number for
October!g5 (all in the same row). With an exact copy, I have found that
=INDIRECT("'"&$C$2&"'!g"&ROW()) does what I want. The problem is, my file is
constantly growing, and I am constantly inserting new rows for new accounts.
When I do this, each account will get incorrect information from the row
above. Is there a way I can use vlookup or another function to make sure the
same information is pulled for each client, even after new rows are inserted.

Thanks in advance.

I get get a spreadsheet each day that gives me orders by day for the year by
Offer #.
If there was no order on a particular day for an offer, it skips that day.
I want to insert the missing day in the correct row and auto insert "0" for
number of orders and $. Ex. -in the data below, there were no orders beetween
12/23 and 12/27. I want to automatically insert rows with the missing date
and "0" for orders and Deamand. Is there function or snippet of code to
help???

Thanks,
Tom

PX05C1A* PX05C2A*
OrderDate Orders Demand Orders Demand
12/23/04 1 $39.00 0 $0.00
12/27/04 4 $214.50 0 $0.00
12/28/04 2 $68.00 0 $0.00
12/29/04 1 $118.00 0 $0.00
12/30/04 10 $799.50 0 $0.00

I have invoice template that each row is formatted, and worksheet protected.
need a code to auto inserts row and copys formulas and formats from above
row. so I can increase the number of line as I finish entering, it creates
another formatted row below
George

I get get a spreadsheet each day that gives me orders by day for the year by
Offer #.
If there was no order on a particular day for an offer, it skips that day.
I want to insert the missing day in the correct row and auto insert "0" for
number of orders and $. Ex. -in the data below, there were no orders beetween
12/23 and 12/27. I want to automatically insert rows with the missing date
and "0" for orders and Deamand. Is there function or snippet of code to
help???

Thanks,
Tom

PX05C1A* PX05C2A*
OrderDate Orders Demand Orders Demand
12/23/04 1 $39.00 0 $0.00
12/27/04 4 $214.50 0 $0.00
12/28/04 2 $68.00 0 $0.00
12/29/04 1 $118.00 0 $0.00
12/30/04 10 $799.50 0 $0.00


No luck finding an answer? You could always try Google.