Inserting a New Row

[suriyahi]

Hello..I am looking to insert a new row based on a criteria. In Col. 'K', I have values '0' and '-1'. I have the column sorted so that all the '0' are sorted first then follows the '-1'. What I want is right after the last row where cell value in COl. 'K' is '0', insert a new row and type in 'Sales' in Col. 'K'. For example:

Before:

FruitsDeptStockApple010Orane020Papaya030Mango-140Strawberry-150Banana-160

After:

FruitsDeptStockApple010Orane020Papaya030 SALES Mango-140Strawberry-150Banana-160

Thank you in advance!:help

[suriyahi]

Attaching a sample copy.:banghead:

[GTO]

Hey Suriyahi,

I didn't insert a whole row, and of course adjust the sheet codename or tab name as req'd.

Have a good day,

Mark


Sub InsertSales()

Dim strRow As String

strRow = Sheet1.Range("K:K").Find(What:="-1", After:=Sheet1.Range("K1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext).Row
Sheet1.Range("J" & strRow & ":L" & strRow).Insert Shift:=xlDown
Sheet1.Range("K" & strRow).Value = "SALES"

End Sub

[Bob Phillips]

Your example uses column F not K as stated, so I have updated GTO's code to reflect this, and also to use a Long not a string (?) to store the found row, and added error handling in case it is not found



Dim nRow As Long

With Sheet1

On Error Resume Next
nRow = .Range("F:F").Find(What:="-1", _
After:=.Range("F1"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext).Row
On Error GoTo 0
If nRow > 0 Then

.Range("E" & nRow).Resize(, 3).Insert Shift:=xlDown
.Range("F" & nRow).Value = "SALES"
End If
End With