| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert row on change of field - with input selection - not whole column
Sub ModifiedTest()
Dim myRow As Long Dim StrtRow As Long Dim myCol As Integer Dim myRange As Range Set myRange = Application.InputBox( _ Prompt:=Prompt, _ Title:=Title, _ Default:=ActiveCell.Address, _ Type:=8) 'Range selection myCol = myRange.Column StrtRow = myRange(65536).End(xlUp).Row For myRow = StrtRow To 2 Step -1 If Cells(myRow, myCol).Value < Cells(myRow - 1, myCol).Value Then Cells(myRow, myCol).EntireRow.Insert End If Next myRow End Sub What wrong with my range - I want only a selection from a column.or can somebody propose me an alternative macro to make it work for a selection in a column only. Thxs Original code is as per below: Sub TryNow() Dim myRow As Long Dim StrtRow As Long Dim myCol As Integer Dim myRange As Range Set myRange = Range("D:D") myCol = myRange.Column StrtRow = myRange(65536).End(xlUp).Row For myRow = StrtRow To 2 Step -1 If Cells(myRow, myCol).Value < Cells(myRow - 1, myCol).Value Then Cells(myRow, myCol).EntireRow.Insert End If Next myRow End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert row on change of field - with input selection - not whole c
Hi,
Try: StrtRow = Cells(Rows.Count, myCol).End(xlUp).Row HTH "al007" wrote: Sub ModifiedTest() Dim myRow As Long Dim StrtRow As Long Dim myCol As Integer Dim myRange As Range Set myRange = Application.InputBox( _ Prompt:=Prompt, _ Title:=Title, _ Default:=ActiveCell.Address, _ Type:=8) 'Range selection myCol = myRange.Column StrtRow = myRange(65536).End(xlUp).Row For myRow = StrtRow To 2 Step -1 If Cells(myRow, myCol).Value < Cells(myRow - 1, myCol).Value Then Cells(myRow, myCol).EntireRow.Insert End If Next myRow End Sub What wrong with my range - I want only a selection from a column.or can somebody propose me an alternative macro to make it work for a selection in a column only. Thxs Original code is as per below: Sub TryNow() Dim myRow As Long Dim StrtRow As Long Dim myCol As Integer Dim myRange As Range Set myRange = Range("D:D") myCol = myRange.Column StrtRow = myRange(65536).End(xlUp).Row For myRow = StrtRow To 2 Step -1 If Cells(myRow, myCol).Value < Cells(myRow - 1, myCol).Value Then Cells(myRow, myCol).EntireRow.Insert End If Next myRow End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to insert row on Col A field change | Excel Discussion (Misc queries) | |||
| Change Field On Pivot Table Using VBA Input Box | Excel Discussion (Misc queries) | |||
| Macro to change list box input range based on selection made in another cell | Excel Programming | |||
| insert data into macro code from text input field? | Excel Programming | |||
| CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Hybrid Mode
