Inserting A Row Into A Data Table

[Jeffrey]

I have a table of data sorted by column A. Is there a way
to insert a row of data into this table in its proper
sorted position (so that after the insert operation the
data is still sorted)? The method I am using now is to
append the row of data at the bottom of the table, then
resort the table.

Thanks.

 

[steve]

Jeffrey,

It can be done but putting it at the bottom and doing a resort is probably
quicker and easier.

 

[Tom Ogilvy]

Sub AAtester2()
Dim rng As Range
Dim res As Variant
Dim newValue as Long
NewValue = 1
If NewValue < Range("A2") Then
Rows(2).Insert
Range("A2") = NewValue
Else
Set rng = Range(Cells(2, 1), _
Cells(Rows.Count, 1).End(xlUp))
res = Application.Match(NewValue, rng, 1)
If Not IsError(res) Then
rng(res + 1).EntireRow.Insert
rng(res + 1).Value = NewValue
End If
End If
End Sub

If these are dates, you will need to adjust


Sub AAtester3()
Dim rng As Range
Dim res As Variant
Dim newValue As Date
newValue = DateSerial(2003, 1, 10)
If newValue < Range("A2") Then
Rows(2).Insert
Range("A2") = newValue
Else
Set rng = Range(Cells(2, 1), _
Cells(Rows.Count, 1).End(xlUp))
res = Application.Match(CLng(newValue), rng, 1)
If Not IsError(res) Then
rng(res + 1).EntireRow.Insert
rng(res + 1).Value = newValue
End If
End If
End Sub