Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

row renumbering

Simple problem, just can't remember how...How does one get the row numbers in the spreadsheet to renumber starting at 1 when you have used the first two rows as column headings??


Post your answer or comment

comments powered by Disqus
I have a worksheet with data in some rows of column 3. I need to sequentially number those rows (putting the number in column 2).

I have some existing code, but I believe that using Auto Filter will make it more efficient. The existing code:

	VB:
	
TestCountB = 0 
TestCountC = 0 
UsedRangeB = 4 'Initializes the start of numbering range
UsedRangeC = 4 
 
Application.ScreenUpdating = False 
 'Test if there are filled cells in a sample of the range in
 'Column B and C.
TheSheet.Activate 
TestCountB = Application.WorksheetFunction.CountA(TheSheet.Range("B5:B500")) 
TestCountC = Application.WorksheetFunction.CountA(TheSheet.Range("C5:C500")) 
 
 'Finds last filled cell in Columns B and C
LastNonEmptyCellB = TheSheet.Range("B65536").End(xlUp).Address 
LastNonEmptyCellC = TheSheet.Range("C65536").End(xlUp).Address 
RangeC = "C5:" & LastNonEmptyCellC 'Sets the range
RangeB = "B5:B" & Right(LastNonEmptyCellC, Len(LastNonEmptyCellC) - InStr(2, LastNonEmptyCellC, "$")) 
 '  RangeB = "B5:" & LastNonEmptyCellB
UsedRangeC = TheSheet.Range(RangeC).Rows.Count 
UsedRangeB = TheSheet.Range(RangeB).Rows.Count 
 
UsedRangeB = UsedRangeB + 4 'Sets the total row count
UsedRangeC = UsedRangeC + 4 
 
If TestCountC > 0 Then 'Before numbering, filled cells in Col C?
    RangeColB = "B5:B" & UsedRangeC 
    TheSheet.Range(RangeColB).Select 
     
     'Checks if text is in a cell in Item Name column (Offset one
     'column to right from Check # column and sequentially moves down
     'one row until the end of the selection.
     'This code sequentially numbers the item in column C with a number in
     'Column B.
     
    For Each Cell In Selection 
        If Trim(Cell.Offset(0, 1)) = "" Then '13 May 2010 - added check to make blank cells really blank
            Cell.Offset(0, 1).Value = "" 
        End If 
        If Application.WorksheetFunction.IsText(Cell.Offset(0, 1)) = True Then 
            Cell.Value = NumSeq 
            Cell.HorizontalAlignment = xlCenter 
            Cell.VerticalAlignment = xlTop 
            NumSeq = NumSeq + 1 
        Else 
            Cell.Value = "" 
        End If 
    Next Cell 
     '  Else
     '    MsgBox ("Text must be in the Item Name column prior to adding a number sequence to Check #")
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My attempts at using the auto filter give me a sheet that's filtered, but I end up numbering every row, or only one row.

	VB:
	
 
With TheSheet 'filter only those rows with data in ItemName
    .AutoFilterMode = False 
    .Range("A4:C4").AutoFilter Field:=ItemCol, Criteria1:="" 
    Set FilterRange = .Cells.SpecialCells(xlCellTypeVisible) 
    Set Used = Intersect(FilterRange, .Range("a4:c50000")) 
    For Each Cell In Used 
        .Cells(Cell.Row, 2).Value = NumSeq 
         'issue is here^^^ Cell.Row is not returning what I expect it to
        NumSeq = NumSeq + 1 
    Next Cell 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It loops through each filtered row, renumbering it until the number = the Excel row number, then it moves on to the next filtered row and repeats the process.

Am I correct in thinking that filtering and looping on the results will be quicker than the current method, and if so, what the heck do I need to do to fix the code? Also, if there are any tips on an even better way, I'm open to those, too.

TIA!
FreeMan

I have a spreadsheet full of project tasks, each numbered chronologically. I would like to create a button which adds a new project task (inserts a new row), numbers it accordingly, and renumbers all subsequent tasks.

I believe the task would go something like this:

1) user clicks button and receives prompt: "after which action will the line item occur?". The user enters the number.
2) The line is added accordingly.
3) All subsequent line items are renumbered (+1).

I would greatly appreciate if anyone could provide some direction. I have a decent knowledge of VBA, but I am not nearly as efficient as I'd like.

Hello everybody. I have a spreadsheet where row 1 and row 2 I am using for headers. I have frozen rows 1 and 2 so the header is always visible when I enter data. I was wondering if there is a way to renumber the rows so the headers does not contain the numbers 1 and 2. Any help anybody can give would be greatly appreciated. Thanks.

See the attached.

Here is what i'm trying to do.

In the "Amend Existing Questions" sheet, a user will specify in the top 3 input fields (cells C3,5,7) a question they wish to update. C9 and 11 show them the current values of some specific columns. C13 and 15 are where they enter the new values. row 18 captures the data in the same format as the destination sheet. "Question List"

i've tried multiple macros to be able to overwrite the data in "Question List" for the relevant line, with the data in row 18 of the "Amend Existing Questions"

However, the macros when I then test ot check it works, are failing for other fields in the list.

Note the following additional information. The "Questions List" can get larger (due to the "Add New Question" tab and macro.) the spreadsheet needs to update at the click of a button, I do not want the user to have to copy and paste the data manually.

The next phase will be to delete and renumber the Question list "IF" a question is no longer required. For now I plan to use the update sheet ot do this and make the question obsolete.

Hi all

I am looking for a formula to number rows in A1:A500
I would like to skip a row when B1:B500 cells are empty
then skip that row and start renumbering from 1
This is what I tried so far
=If(b9>"",countif($B$9:$B9,">"""),"")
This one has me stumped
any help would be apreciated

I just need a bit of help with my logic.
I have a spreadsheet with a list of jobs in it, with a bunch of columns describing various aspects of the job. (Supervisor, department, etcetera.)
I also have a priority column.
What I'm looking for is to have my list renumber itself when I change the priority on jobs.

For example:

Priority Job Name Supervisor
1 Dig Ditch Ben
2 Fix Window Sira
3 Check C2 Cory
4 Check SS Ben

Let's say I change "Check C2" to be priority 1, I would like my priority list to re-order itself to:

Priority Job Name Supervisor
2 Dig Ditch Ben
3 Fix Window Sira
1 Check C2 Cory
4 Check SS Ben

Also, bear in mind that the list might not always be sorted by priority. It is just as likely to be sorted by Supervisor or one of the other columns not mentioned here. Data Validation keeps the numbers whole & positive.

My problem is mostly thus...

I try and capture the change through Worksheet_Change. However, each time it increments one of the priorities, it runs the Worksheet_Change event again, giving me priorities in a nice Fibbonacci-looking sequence. Aesthetically & mathematically interesting, but totally incorrect.
Also, for some reason, if I delete the last job (or just the priority of the last job), it never terminates the code, and runs, incrementing the code and pushing my priorities up & up & up.

This is my code:


	VB:
	
 Range) 
    If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then 
        If Chr(Target.Column + 64) = PriorityColumn And Target.Row > SortRow Then 
            Dim Changed As Boolean 
            Dim Max As Integer: Max = 0 
            Dim i As Integer 
            Dim Maxi As Integer: Maxi = (Find_First_Blank_Job_Line - 1) 
            For i = SortRow + 1 To Maxi 
                If i  Target.Row Then ' Skips renumbering Target
                    If Sheets("Task List").Range(PriorityColumn & i).Value >= _ 
                    Target.Value Then 
                        Sheets("Task List").Range(PriorityColumn & i).Value = _ 
                        Sheets("Task List").Range(PriorityColumn & i).Value + 1 
                    End If 
                End If 
            Next i 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anyone help me out with this?

I have attached an example.

Hi! I've got excel entries with corresponding CheckBoxes. Is it possible to get the position of checkboxes (in this case I only need row number), or automatically renumber all of them from top to bottom each time I start a macro execution? Of couse, I can set "Don't move or size with cells", but I think it would be better still to be able resize cells at any time, so that controls adjust to these changes...

hello all~I need to know how to fix this. I have a worksheet and I have not used a header but went ahead and put in the title of the worksheet on my row #1 so that my boss could see it so he knows which worksheet he is looking at. After inputting my row headers...my first row of data starts on row 6. My main issue is getting a count for the rows not including the header rows, or to start the row count to 1 on the data rows.

A B C D E
1 Title of Worksheet
2

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?

I have about 1,000 rows and want to filter out only specific rows that contain a specific value. This is done in a macro. What I want to know is how do I automatically renumber the rows once it is filtered. Perhaps another macro? Thank you in advance.

dayday

All-

Thank you so much in advance. I acknowledge that this is probably a simple fix, but the answer escapes me.

Workbook Description
The intent is to track the various projects occuring in our department. One sheet is the "in progress"; the other is exactly the same but for "completed". Every other row is a distinct project (odd rows are blank). Columns are the various descriptors such as name, responsible party, comments, etc. A macro in a button creates a new row for a new project and shifts the rest down, re-numbers, etc.

Problems:
1.) My aim is to automate the process of breaking-out any "completed" projects. My initial thought was to create a button (or the like) that the user could push that would indicate to excel to cut that row and move it from the "in progress" worksheet to the "completed" worksheet. A completion could occur in any project row while others still remain in progress.

2.) As a follow up to 1, I have sequentially numbered the rows with the following (accounts for blanks, new projects, etc.):
IF(MOD(ROW(),2),R[-2]C+1,"")
So, this works when I add a row, but goes error #ref when a row is deleted. How can I rewrite this formula such that it will renumber the projects when one is broken-out to the completed worksheet.

Thank you again,

Can anyone advise me how to number the first column of my worksheet.
I want the number to automatically increment when I enter the first cell of
following row. I do not want this number to be entered manually.
I need to be able to delete rows randomly and for the the row numbers to
automatically renumber in numerical order with no numerical gaps.
For example if I have a column 1,2,3,4,5, and delete 3, I would like the
column to automatically renumber as 1234 and not 1,2,4,5.
I hope I have explained myself correctly. I have a copy of Excel 2002
InsideOut, but can't find a method to do this.
Please supply an example if possible.
I tried =ROW() but cna't get it into all the rows to test if I can maintain
my numerical record numbering when a ROW is deleated.
Please Advise
Thanks
Dermot

Another relevant question...although basic.....
I will try to keep it brief....
I know how to enter the formula in a cell
I could enter the formula in say a group of 20 Cells manually...
I do not know how a shortcut method to enter the formula in all the cells
beyond say row 4 to say infinity.....this would save me the need to return at
a later date to
enter the formula manually every time the rows I have previously entered
enter the formula in run out (Used as a record).
I have tried experimenting with copy and paste but do not get the results I
expected.
Thanks
Dermot

I have a custom list of letters A-Z which enables me to type an 'a' in cell
E3 and drag down the fill handle to get the other characters. However, when
rows are hidden I would like to ignore hidden cells and re-alphabetize just
the visible cells.

I am able to do this with numbers with the following recorded macro:
Sub mcrRenumVisCells()
'Renumbers visible cells -- ignores hidden cells
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay,
Step:=1, Trend:=False
End Sub

Is there anything I can do adapt this for my alphabets? Thanks.

Hi i have a large excel file with thousands of rows of data. I want to renumber the data whilst having a value of 10 between each in the format M00010,M00020,M00030 etc. These values are all in column A and where i have an M00010 value in column A i have a Text string in column B. Between the values in column A i have different numbers of blank rows that i need to keep. Sometimes 2 sometimes 3 or more.

I have tried using Filter but this doesn't really help as i can't use FILL to change the numbers.

Any help would be appreciated.

Can anyone advise me how to number the first column of my worksheet.
I want the number to automatically increment when I enter the first cell of
following row. I do not want this number to be entered manually.
I need to be able to delete rows randomly and for the the row numbers to
automatically renumber in numerical order with no numerical gaps.
For example if I have a column 1,2,3,4,5, and delete 3, I would like the
column to automatically renumber as 1234 and not 1,2,4,5.
I hope I have explained myself correctly. I have a copy of Excel 2002
InsideOut, but can't find a method to do this.
Please supply an example if possible.
Regards
Dermot

I'm running Excel 2008 for Mac on a MacBook Pro with the Leopard operating system.

When I delete a row in a spread sheet, Excel does not automatically renumber the spreadsheet. Instead, I am left with gaps in row numbers (for example, Row 18 is followed by Row 21).

I'd like to know how to get Excel to renumber the spreadsheet, and what needs to be changed in my settings to make this happen automatically in future files.

Thanks.

Hi,
I'm new to excel and I've created a spreadsheet that has multiple drop down list and I would like to hide rows based upon users responses in the drop down list. For example in cell B1 I have a drop down list with a yes/no option. If a user selects "no" I want rows 2-5 hidden and if they select "yes" I want rows 2-5 available. Then in cell B6 I have another drop down list with a yes/no option. If a user then selects "no" I want rows 7-10 hidden and if they select "yes" I want rows 7-10 available. I have it working for cell B1 but for some reason I can't get it to also work for cell B6. I would like to have about 15 drop down list in the spreadsheet that hides rows based upon users answers. Also the drop down list options will not always be a yes/no option. There could be up to 5-6 options in the drop down list.

I'm using Office 2010

Any help would greatly be appreciated

Thanks

Excel: VBA

hi im just really new .stuck on this projek to add number to dtabase from multiple box to each row down.please look athe attachment.im not sleeping trying to do it but i cant.i want to fill form with number that record to database of every 4 digit to row down for another for digit.i able to do for 1 row only but cannot the other number box to next row n database.tq

Hello All,

I am trying to add a value to rows with data - such as a row counter displayed in column A (i.e. A1 = 1, A2 = 2, A38 = 38). After the loop is done I want it to erase the rows that do not have any value in column B.

Thanks for the help.

I’m having a bit of trouble finding exactly what I’m wanting but also having trouble following posted examples/solutions I’ve read that might be able to be applied to what I am trying to do (novice with VBA).

I have an Excel spreadsheet with data laid out like this:
DR acct CR acct Amount Amount2 43979.001.051 40910.001.051 (1,802.00) 1,802.00 43979.011.049 41961.011.049 5,605.00 (5,605.00) 43979.002.041 41962.002.041 (600.00) 600.00 43979.002.046 42963.002.046 2,345.00 (2,345.00)
I want a quick and easy way to put it into two columns so that it looks like this as a final version:
Account Amount 43979.001.051 (1,802.00) 40910.001.051 1,802.00 43979.011.049 5,605.00 41961.011.049 (5,605.00) 43979.002.041 (600.00) 41962.002.041 600.00 43979.002.046 2,345.00 42963.002.046 (2,345.00)
My data is voluminous, so I’m wanting a way to do this other than, say, manually copying and transposing the account numbers row by row, and then moving the amounts where they should go.

Thanks anyone.

Tricia

Hi Everyone i have a small problem in the below code, the code is working but it cut the one & only the data in row number 3, & i want to change it to any raw i will sellect or link that transaction with one word like canceled in any cell in the sheet.

please help.

Copy some Row from up to the end of the sheet very good but only from specific Rows.

	VB:
	
 Cpy() 
    With Sheets("am").Rows(3) 
        .Copy Destination:=Sheets("am").Range("A" & Rows.Count).End(xlUp).Offset(1) 
        .ClearContents 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


First of all, I know there is a way to do so using paste special>transpose. But it doesn't work for my case.

Ok here's part of my worksheet :

There are around 100+ entries of the same format.

What I want to do is change all of the rows into columns like this :

Where only the Name/Department/Faculty etc only appear once and the respective values go into the respective collumns.

Is there a VBA code to do it or can it be done without? Appreciate any help given!

Hi,

I have a worksheet consist of 10k++ lines and here is my line sample:

1. 8-25B2-BR-20160-H25 - Sht 1/5 - Rev.1 - Size - Thickness.

column A - need to fill in numbering
column B - Diameter
column C - Area code
column D - System
column E - line number
column F - sheet no.
column G - revision no.
column H - Pipe Size
column I - Thickness

1. Need a macro to insert a blank row if any of system, line number, sheet no, pipe size or thickness change.
2. auto numbering in column A

Hi,

I'm struggling on the next problem:

I have a worksheet with 700 rows. each row contains cells with data (collumns A to BI). I apply the Autofilter for each production week/day combination (We have 4 different production weeks, e.g. A, B, C and D and 6 workdays 1, 2, 3, 4, 5 and 6)
So first filter is week A and day 1, second is week A day 2 and so on.
With the values of a number of cells of each of the visible rows within the autofilter I would like to create a new worksheet in another workbook.
The new worksheet always has a totally different layout from the original sheet so the values of these cells will have to be pasted in another cell (e.g. content of a cell in column A should be pasted in cell $M$1, content of a cell in collumn T will go to $A$1
I tried it with the build in macro recorder, but that keeps copying the same row.

It would be so cool if it is possible to do the next with vba:

Use a template for each new destination worksheet.
Go to first visible row in Autofiltered worksheet, copy the values of the needed cells to the right cells in the ´template´worksheet.
Copy the filled out ´template´ sheet to an other sheet, delete all entries in the cells where values were copied to (not the ´template´ values) in the template sheet and go to the next row in the Autofiltered worksheet.

I will try to make an example of both worksheets and post it here.

Many thanks in advance,

Gerald Baalhuis


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