Need to move Chennai to 7th row and Bangalore to 17th row of existing data
Need to move Chennai to 7th row and Bangalore to 17th row of existing data
Last edited by chandy123987; 08-06-2018 at 01:41 AM.
Is it a one shot or do you need to do it regularly ?
- Battle without fear gives no glory - Just try
Regularly on monthly basis
Perhaps next code
Option Explicit Option Base 1 Sub Treat() Const WkCol = "B" Dim KWd, KRw Dim K As String Dim Rw As Integer Dim F Dim i As Integer KWd = Array("CHENNAI", "BANGALORE") KRw = Array(7, 17) For i = 1 To UBound(KWd, 1) K = KWd(i) Rw = KRw(i) Set F = Columns(WkCol).Find(K, LookIn:=xlValues, Lookat:=xlWhole) If (Not F Is Nothing) Then Rows(F.Row & ":" & Rw - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End If Next i End Sub
It works well but i need this for multiple sheets.
Is there a list or is it for all sheets ??i need this for multiple sheets.
Perhaps next code it works for all unhidden sheets
Option Explicit Option Base 1 Sub Treat() Const WkCol = "B" Dim KWd, KRw Dim K As String Dim Rw As Integer Dim F Dim i As Integer Dim WS As Worksheet KWd = Array("CHENNAI", "BANGALORE") KRw = Array(7, 17) For Each WS In Sheets If WS.Visible = True Then With WS For i = 1 To UBound(KWd, 1) K = KWd(i) Rw = KRw(i) Set F = .Columns(WkCol).Find(K, LookIn:=xlValues, Lookat:=xlWhole) If (Not F Is Nothing) Then .Rows(F.Row & ":" & Rw - 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End If Next i End With End If Next WS End Sub
Last edited by PCI; 08-03-2018 at 11:32 AM.
it works on all sheets. Thanks
You are welcome.
itwas good to have a sample file ...!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks