Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function to insert rows on a change in a cell
Is it possible to automatically insert a blank row after a change in a cell
value |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function to insert rows on a change in a cell
Not using a function or a formula. You can only do that with an event macro.
Pete "Subhash" wrote in message ... Is it possible to automatically insert a blank row after a change in a cell value |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function to insert rows on a change in a cell
Thanks Pete
What is an event macro ? "Pete_UK" wrote: Not using a function or a formula. You can only do that with an event macro. Pete "Subhash" wrote in message ... Is it possible to automatically insert a blank row after a change in a cell value |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Function to insert rows on a change in a cell
See Chip Pearson's site for more on event macros.
http://www.cpearson.com/excel/Events.aspx This would be an example of event code.................... Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim num As Integer num = 1 On Error GoTo stoppit If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub Target.Offset(1, 0).Resize(num).EntireRow.Insert stoppit: End Sub When you manually change the data in A1, you will insert a blank row below that. Right-click on the worksheet tab and "View Code" Copy/paste into that sheet module. Alt + q to return to the Excel worksheet window. BUT...................if your question was to insert a row at every change in value down a column. i.e. column A has values of abc abc abc def def def ghi ghi ghi You want to insert a row at each change you would use a regular macro. Sub InsertRow_At_Change() 'Sandy Mann July 1st, 2007 Dim LastRow As Long Dim X As Long LastRow = Cells(Rows.Count, 1).End(xlUp).Row Application.ScreenUpdating = False For X = LastRow To 3 Step -1 If Cells(X, 1).Value < Cells(X - 1, 1).Value Then If Cells(X, 1).Value < "" Then If Cells(X - 1, 1).Value < "" Then Cells(X, 1).EntireRow.Insert Shift:=xlDown End If End If End If Next X Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 18 Mar 2008 18:34:00 -0700, Subhash wrote: Thanks Pete What is an event macro ? "Pete_UK" wrote: Not using a function or a formula. You can only do that with an event macro. Pete "Subhash" wrote in message ... Is it possible to automatically insert a blank row after a change in a cell value |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to insert rows based on change in data | Excel Worksheet Functions | |||
how do i insert rows in a cell | Excel Worksheet Functions | |||
how to change the insert function key to always be an = sign | Excel Discussion (Misc queries) | |||
Change INSERT FUNCTION icon left of formula bar to = sign? | Setting up and Configuration of Excel | |||
Can I change the Insert Function button to an equals sign? | Excel Discussion (Misc queries) |