View Full Version : Inserting a New Row
suriyahi
10-06-2008, 12:25 PM
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
10-06-2008, 12:31 PM
Attaching a sample copy.:banghead:
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
10-06-2008, 02:47 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.