Free Microsoft Excel 2013 Quick Reference

Retain Auto Number Sequence After Row Insert

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


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

I have a sheet that has an average formula in cell c2. The data will only be in cells c9 to c13 but at the moment are empty. Ineed to insert a row after c9, insert data into c9 and calculate. Then insert another row and recalculate after inserting data in c9 again. There will be other rows after c13 but I only need to average the first five entrys in c9 to c13.

Hi every one...I want some help in excel 2007..
I have created a list of number sequence in rows i-e (001-150)...
now I want to add some text in this whole sequence before and after the digit...
for example:-
(sahil001@hotmail.com to sahil150@hotmail.com)

I need a shortcut to do this instead of writing separate text in each row...
Kindly help me out...
thanks...

Sahil......

Hi

I do not have a AddIn pdf converter in excel 2007,and was thinking of saving a filled in worksheet as jpeg. with a auto numbering sequence. Hope a good brillaint mind could help me with a code.

I saw a code working on extacting and saving multiple workbooks from one worksheet/s sources. but no idea how to auto set the auto numbering as it is saved into the folder. I am limited to create the macro on saving the details to a consolidating sheet, and can't figure out how to autosave the filled in Form in sequence - lets say SR0001 on so on. Any ideas please.

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

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!

I have a worksheet with header text in rows 1 thru 5 and summary data in last
4 rows (variable row number dependent on number of data rows). How can I
prevent users from inserting rows in the header and summary sections, but
allow them to insert rows after row 6 and before the summary rows?

Dear all friends,

Thanks for helping me so far.

I was just thinking if it is possible to auto number the autofilter
result.

For Eg,

Sr. No Answer Name
Yes X
No X
Yes X
Yes X
No X

If I auto filter the above range on "Answer" then is it possible to
get incremental values (Default: 1) in Column "Sr. No" depending upon
the result rows displayed by autofilter

For Eg,
When I select "Yes"

Sr. No Answer Name
1 Yes X
2 Yes X
3 Yes X

And When I select "No"

Sr. No Answer Name
1 No X
2 No X

Please let me know if it is possible and if yes then any suggestions
for how.

With Regards,

Ashish

I have a worksheet with header text in rows 1 thru 5 and summary data in last
4 rows (variable row number dependent on number of data rows). How can I
prevent users from inserting rows in the header and summary sections, but
allow them to insert rows after row 6 and before the summary rows?

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

Hello all:

I have the following formula in every cell in my "A" column:
=IF(NOT(ISBLANK(B1)),COUNTA(B$1:B1),"")

This serves the purpose so that whenever I type something in column B, it
will auto number the entries consecutively. When i delete a row, the
numbering still works and all the numbers are reorganized but still
consecutive.

However when i insert a row, the formula does not exist in the new cell in
column A.... and so my whole numbering gets thrown off.. Is there a way to
make it such that there is auto numbering when i insert rows? Perhaps by
having the formula above placed in the cell as soon as i insert a new row?
Or something that makes sure each row in column A has a similar formula that
allows this kind of auto-numbering?

Please, Any help would be appreciated,

Terrel Lobo

Hi! I know there is a way to do this but I cannot remember how to do it....

I know I did it in the past and can't for the life of me remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want to insert 4
rows after each number and then copy that number into each new row.

Any thoughts how to accomplish this automatically?

Denise

For example: I would like my list of names to start with row number 1, after
I have entered the headings for names, addresses, etc.

Hi,

after days and days of research i have hit a stumbling block with my code and i hope you can spare a minute to help me.

i have a workbook with two worksheets, Documents and Workings. The user can fill in the userform i have created so that it fills in the worksheet in documents accordingly.

My code is as folows:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Documents")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a Doc Number
If Trim(Me.TextBoxDocNo.Value) = "" Then
Me.TextBoxDocNo.SetFocus
MsgBox "Please Create a Document Number by the using the Create Button"
Exit Sub

End If 'check for a Date
If Trim(Me.txtCal.Value) = "" Then
Me.txtCal.SetFocus
MsgBox "Please enter a Date"
Exit Sub

End If

'check for a Description

If Trim(Me.txtDescription.Value) = "" Then
Me.txtDescription.SetFocus
MsgBox "Please enter a Description"
Exit Sub
End If

'check for a Location

If Trim(Me.txtLocation.Value) = "" Then
Me.txtLocation.SetFocus
MsgBox "Please enter a Location"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCal.Value
ws.Cells(iRow, 2).Value = Me.TextBoxDocNo.Value
ws.Cells(iRow, 3).Value = Me.txtDescription.Value
ws.Cells(iRow, 4).Value = Me.txtLocation.Value
ws.Cells(iRow, 5).Value = Me.ClassDoc.Value
ws.Cells(iRow, 6).Value = Me.ScheduleDoc.Value
ws.Cells(iRow, 10).Value = Me.edited.Value
ws.Cells(iRow, 11).Value = Me.unedited.Value
ws.Cells(iRow, 14).Value = Me.txtNotes.Value


'ws.Cells(iRow, 7).Value = Me.ClassDoc.Value
'ws.Cells(iRow, 5).Value = Me.ClassDoc.Value

If edited.Value = True Then Range("H" & iRow) = "*EDITED*"
If unedited.Value = True Then Range("I" & iRow) = "*UNEDITED*"
If TCPS.Value = True Then Range("G" & iRow) = "Test"
If Attached.Value = True Then Range("L" & iRow) = "*"

'If EditedMG6C = True Then [G4] = "MG6E"
'If EditedMG6C = False Then [G4] = ""




Me.TextBoxDocNo.SetFocus
Me.txtCal.SetFocus
Me.txtLocation.SetFocus
Me.txtDescription.SetFocus

Unload Me
frmDataEntry.Show

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub CommandButton3_Click()
Dim iRow As Long
Dim ws As Worksheet

Dim x As Integer
Dim bIncrement As Boolean
Set ws = Worksheets("Workings")

'find last data row from database
iRow = ws.Cells(Rows.Count, 12) _
.End(xlUp).Row

x = 0
Sheets("Workings").Select
Do
If IsEmpty(Range("L1").Offset(x + 1, 0).Value) Then
Range("L1").Offset(x + 1, 0).Value = Range("L1").Offset(x, 0) + 1
bIncrement = True
End If
x = x + 1

Loop Until bIncrement = True


Me.TextBoxDocNo.Text = "D" & ws.Cells(iRow, 12).Value + 1
Sheets("Documents").Select

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the Close Input Form Button!"
End If
End Sub
Private Sub cmd1_Click()
CalendarForm.Show
End Sub
Private Sub UserForm_Activate()
Dim iRow As Long

Dim ws As Worksheet

Dim x As Integer
Dim bIncrement As Boolean
Set ws = Worksheets("Workings")

'find last data row from database

iRow = ws.Cells(Rows.Count, 12) _
.End(xlUp).Row

x = 0

Sheets("Workings").Select
Do
If IsEmpty(Range("L1").Offset(x + 1, 0).Value) Then
Range("L1").Offset(x + 1, 0).Value = Range("L1").Offset(x, 0) + 1
bIncrement = True
End If
x = x + 1

Loop Until bIncrement = True




Me.TextBoxDocNo.Text = "D" & ws.Cells(iRow, 12).Value + 1
Sheets("Documents").Select

End Sub
What i am trying to achieve is auto numbering in column B in Documents with D1, D2, etc.

Each time the userform is opened or restarted or populated by a the command button it populates the D1, D2 etc textbox on the userform and then the form submits the alll the data to the next free row in the worksheet.

As you will see i have with my very limited knowledge created a work round utilising the workings worksheet to populate column L and then transfers this to the userform. This code relys on the cell above A2 to be a number.

Is it possible to dispense with my workround and get the userform to populate the textbox automatically as it reads from column B in the documents worksheet, collects the last D number used and allocates the next one, if the form is blank then it should allocate D1 in the first instance.

I do hope i have made some sense and that this last hurdle can be overcome, thank you in anticipation,

regards

Lee

Hi! I know there is a way to do this but I cannot remember how to do it....

I know I did it in the past and can't for the life of me remeber how I
accomplished it.

I'm using Excel 2003. I have a long column of numbers. I want to insert 4
rows after each number and then copy that number into each new row.

Any thoughts how to accomplish this automatically?

Denise

Hi, I did trawl through a few hundred similar topics but could not find my answer.. here goes;

I am using Excel 2007 on a win7 machine and I am manipulating data I cut and paste into a spreadsheet.

I need to check the number of empty rows after the character "Kg" or "L" or "CL" that is preceeded by a number.. so it could be "1 Kg" or 0,5 Kg" or "0,5 L" but there seems to always be a space before the Kg or L if that helps.

I need there to be 6 empty rows between the Kg and the cell that contains a price.. for example "3.24€"

All my data is in Col "A"

example data:

A1:item description
A2: empty row
A3:brand name
A4: empty row
A5: empty row
A6:1 Kg

what i need it to be is:

A1:item description
A2: empty row
A3:brand name
A4: empty row
A5: empty row
A6: empty row
A7: empty row
A8: empty row
A9: empty row
A6:1 Kg

I might add that the number of rows above the Kg or L or Cl is not constant so I couldnt attack the problem by deleting the empty rows and simply adding the desired number of rows.

I also add that the number of rows after the weight measure might be 2 or 3 or 4, etc but as I said I need it to be six due to some pre written vba I have.

what I am trying to do is align horizontaly that data and it seems that the constant is the weight part of my data.
thanks in advance

I have a list of items for multiple people (territories). I would like an Excel formula that would restart a row numbering sequence every time the territory number changes. The simple spreadsheet attached illustrates the results I would like to obtain.

From Ozgrid I picked up a formula that creates dynamic named ranges. Here is the formula I'm using for my needs:

=OFFSET(CATEGORIES!$B$15,0,0,COUNTA(CATEGORIES!$B$15:$B$65536),1)

My dynamic range begins at B15. There will be many times when a row will need to be inserted at row 15, however, when this happens the formula above changes to $B$16, then $B$17, and so on. I'm confused because I thought the formula was absolute based.

Is there a way to make it stay at B15, or make it step back every time a new row is inserted, or start at B14 (the heading) but exclude the heading from the results of the dynamic range?

Thank you.

Hi All,

I have a requirement where, in one of the column i would like to have an auto numbering (similar to Microsoft access). I know this can be done using Macros, but is there any other better alternative.

Cheers,

Kiran

Hi,

I have about 20 Excel files in which the actual data is in alternate rows. For example:

In file 1 the 1st, 3rd, 5th and so on rows contain data while in file2 the 2nd, 4th, 6th and so on rows contain data. Many of the files also have data in th 1st row and then after skipping 3 rows (for example) the next row containing data is the 5th row.

Assuming the above is clear, I am looking for a code which I can invoke while my active cell is A1. The code should ask the starting row containing data and the number of blank rows between rows containing data...

Given my little VBA knowledge, I have come up with the following pseudo-code and will appreciate help/guidance from someone.

Sub DelAlternateRows( )

Dim LastRow As Long, r As Long

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

For Each r = 2 to LastRow step 2
' select the 2nd row assuming that the code was invoked when the active cell was A1
' and the assumption that the first row contains data

' Delete the selected row
Next r
End Sub

I know the code is not even close to handle all the cases that I have mentioned and thus this request for help.

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

Thanks

Hi i was writing a macro and i cannot seem to get it to work. i have a data set sorted based on a serial number in column N. what i would like to do is if the number in the row does not match the number in the row above it it would add a new row and based on the serial number imput a header from some yet to be defined cell.


	VB:
	
 1 
    If Sheets("Sheet1 (2)").Range("N" & q)  Sheets("Sheet1 (2)").Range("N" & q - 1) Then 
        Selection.EntireRow.Insert 
    End If 
Next q 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
this is what i have so far and it doesn't seem to be doing what i want. any help is appreciated.

How can I make a incident number based on a date. When inputing the date in col b I want to have auto number in col a. The format I would like to see is YY-0001. The next number would be YY-0002....YY-3243 I need to base the start and end numbers by fiscal year. Starting July 1 and Ending June 30th.

With the current year anything before July 1, 2008 would be 07-#### and on July 1, 2008 and after it would be 08-####.

Thanks in advance.

Rob

I am trying to import an excel sheet into Access database, and leave the autonumber column blank. However, it gives me the following error.

Null value in an auto-number field

How should I avoid having null values in Excel, as ACCESS has to insert its own auto numbers in it.