Is there a way of getting Excel to remember the last highest number entered, so that if a row was deleted, you could enter the next sequential number even if it was in the gap just created by the row just deleted?
Running down a column with A1 +1 comes unstuck if you alter a cell in the range.
How about just using
=ROW()
Looked up =Row() in Help but its still a mystery to me
Might try a sheet change:
[VBA]
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim RowOffset As Long
Dim IndexCol As String
'Set values
RowOffset = 0
'Change the A to the column where you want the numbers to show
IndexCol = "A"
Intersect(ActiveCell.EntireRow, Columns(IndexCol)).Value = ActiveCell.Row + RowOffset
End Sub
[/VBA]
Steve
"Nearly all men can stand adversity, but if you want to test a man's character, give him power."
-Abraham Lincoln
Thanks Steve.
I had come across this one late last night, and while at work I found that if you deleted a row, and added the next highest number, it was duplicated when selecting the next cell down from the las tof the row. Will give yours a trundle and see what runs up the flagpole.
[vba]
Private Sub WorkSheet_SelectionChange(ByVal Target As Excel.Range)
RowOffset = O
Intersect (ActiveCel.Entire.Row, Colummns("A")).Value = ActiveCell.Row + RowOffset
End Sub
[/vba]
OKay, I need to attack this from another method.
Is it possible to have a hidden cell remember the highest value recorded within a range ( even if that highest value has since been deleted)?
The purpose here is to enable a continuace of a numbering pattern within a set range of rows.
Last edited by Aussiebear; 08-06-2006 at 04:48 PM. Reason: Adding sample workbook
I've tried using Max & MaxA for the hidden cell value but as soon as you delete the row with the highest value in it, the value of the hidden cell drops back to the next possible higest value. What I'd like is for the value of the hidden cell to be retained once its equivilant value has been used in the range A4: A28.
Any ideas anyone?
Hi Ted,
You need to add the highest value to A2 initially. The number format can be removed after the first running. If you're going to need 3 figure numbers, you may want to use the formatting alternative.
Regards
MD
[VBA]Sub Macro1()
[a2].NumberFormat = ";;;"
[a2] = [a2] + 1
ActiveCell.Formula = "2006-" & [a2]
'ActiveCell.Formula = "2006-" & Format([a2], "000")
End Sub[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Why is =ROW() a mystery?
You enter into the cell ...
=ROW()
.. just as it's shown there and the row number will appear (it's a cell formula).
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Zack, over time I will be re-using that same row, so today it might be 2006-01 and in a month's time it could be 2006-33.
Malcolm, The numbers in the range will start with 2006-01 and build until we change to a new year where it will start with 2007-01 and build upwards
Then I would use
[VBA]
ActiveCell.Formula = Year(Now()) & "-" & Format([a2], "000")
[/VBA]
remembering to reset A2 to 0 after your New Year celebrations.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Malcolm, your code works but only for the first time you use the range. This is why the "=Row() function doesn't work as well. The objective here is to be able to use any of the rows within a Range, (say A2:A25), more than once and yet be able to continue the numbering in sequence from the last highest number used.
Perhaps the only way around this issue is to set up a user form, (Called by a button on the main sheet) which has a counter type function included.
A sample is required
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
You need to be careful with your teminology. There will be no gap if a row is deleted. There will be a gap if a row is cleared.Originally Posted by Aussiebear
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sorry for the wrong terminology. Your code works very well and is exactly what I'm looking. Thank you Malcolm (You shold change your name to "Solver")
OKay, I'm back to square one again. It's become like a zig saw puzzle with all black pieces....... Can someone please wave their magic wand over this?
I'm away for at least a day, but the Admin sheet gives a rough idea of what its meant to do.
Here's a userform solution
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thank you Malcolm.
Userform updated
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote
