[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Inserting / deleting rows when macro hits a certain term in Column A

Posted on 2014-08-08
9
Medium Priority
?
266 Views
Last Modified: 2014-08-08
Dear Experts:

I would like to run two macros on Column A of the active worksheet that performs the following task

1. Whenever the macro hits the term 'Website', the row directly below that row is to be deleted

2. Whenever the macro hits the term 'Name' a row directly below that row is to be inserted.

It should be two macros not a combined one.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
0
Comment
Question by:AndreasHermle
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 54

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 664 total points
ID: 40248302
Hi,

pls try

Sub DeleteRowAfterWebsite()

Set rngCellFound = Range("A1")

For Idx = 1 To WorksheetFunction.CountIf(Columns(1), "Website")
    Set rngCellFound = Columns(1).Find(What:="Website", After:=rngCellFound, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
        rngCellFound.Offset(1).EntireRow.Delete
Next Idx

End Sub

Sub AddRowAfterName()

Set rngCellFound = Range("A1")

For Idx = 1 To WorksheetFunction.CountIf(Columns(1), "Name")
    Set rngCellFound = Columns(1).Find(What:="Name", After:=rngCellFound, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
        rngCellFound.Offset(1).EntireRow.Insert
Next Idx

End Sub

Open in new window

Regards
0
 
LVL 24

Accepted Solution

by:
Steve earned 668 total points
ID: 40248304
You can just as easily loop down the rows:

Sub ChangeSheetWebsite()

Dim lastRow As Long, x As Long
Dim ws As Worksheet

Set ws = ActiveSheet

lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For x = 1 To lastRow
    If ws.Cells(x, "A") = "Website" Then
        ws.Rows(x + 1).Delete
    End If
Next x

End Sub

Open in new window

Sub ChangeSheetName()

Dim lastRow As Long, x As Long
Dim ws As Worksheet
Set ws = ActiveSheet

lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

For x = 1 To lastRow
    If ws.Cells(x, "A") = "Name" Then
        ws.Rows(x + 1).Insert
    End If
Next x

End Sub

Open in new window

U--Example.xlsm
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 668 total points
ID: 40248310
Public Sub DeleteRows()
    Dim Max As Integer
    Dim Row As Integer

    Max = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    For Row = Max To 2 Step -1  ' Start at the bottom, go up
        If LCase(Cells(Row - 1, 1).Value) = "website" Then
            Cells(Row, 1).EntireRow.Delete
        End If
    Next Row
End Sub

Public Sub InsertRows()
    Dim Max As Integer
    Dim Row As Integer

    Max = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    For Row = Max To 2 Step -1  ' Start at the bottom, go up
        If LCase(Cells(Row - 1, 1).Value) = "name" Then
            Cells(Row, 1).EntireRow.Insert
        End If
    Next Row
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Assisted Solution

by:Steve
Steve earned 668 total points
ID: 40248314
Working backwards, though nescessary if working with the active row, is not nescessary if working with rows below where you are.
Rows should not be Integers integer will not go high enough if there are more than the old xls limit of 65526 (Integer limit = 32767, so use long)
The use of Lcase is good, if relevant, and may point towards using Like:
If LCase(Cells(Row - 1, 1).Value) Like "*name*" Then

Open in new window

0
 

Author Comment

by:AndreasHermle
ID: 40248377
Hi Steve,

Thank you very much for your great help. I guess the second code of yours 'Insert_Rows' is to be tweaked ...
... since there is no blank row insertion below the last occurrence of the cell containing the term 'name'

But actually this is just a very minor issue with this code, otherwise everything is perfect.

Regards, Andreas
0
 

Author Comment

by:AndreasHermle
ID: 40248393
Hi Steve,

forget about it, a second trial produced the right results.

Regards, Andreas
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40248398
Replace xl Part with xlWhole

Sub DeleteRowAfterWebsite()

Set rngCellFound = Range("A1")

For Idx = 1 To WorksheetFunction.CountIf(Columns(1), "Website")
    Set rngCellFound = Columns(1).Find(What:="Website", After:=rngCellFound, _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
        rngCellFound.Offset(1).EntireRow.Delete
Next Idx

End Sub

Sub AddRowAfterName()

Set rngCellFound = Range("A1")

For Idx = 1 To WorksheetFunction.CountIf(Columns(1), "Name")
    Set rngCellFound = Columns(1).Find(What:="Name", After:=rngCellFound, _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
        rngCellFound.Offset(1).EntireRow.Insert
Next Idx

End Sub

Open in new window

0
 

Author Closing Comment

by:AndreasHermle
ID: 40248408
Dear all,

great support from all of you. Thank you very much for your professional help. I really appreciate it. Since all of the approaches work just fine, I thought that splitting the points evenly is the best way.

Regards, Andreas
0
 
LVL 24

Expert Comment

by:Steve
ID: 40249350
Indeed Andreas,
an even split seems fair,
there are many ways to perform the task,
this seems to cover the main ones.

ATB
Steve.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question